XML from SQL Data

Category: sql server xml

Question

Mooro1000 on Tue, 12 Jul 2016 16:18:36


Hi this is my first post so forgive me if this is too simple or in the wrong format !

I'm trying to replicate this type of XML Structure from a query in my SQL database, I'd like to create the XML using FOR XML (explicit I guess), but am struggling to find the right method ..

The SQL table is simple and contains 3 columns ..

--create the table

CREATE TABLE #T (CustomerID int, ScoreType varchar(20), Score int)
INSERT INTO #T values (7, 'General', 88)
INSERT INTO #T values (8, 'General', 88)

The resulting XML should look like this ... I've played around a bit but struggling to find the right methods to break it out .. any help is truly appreciated. Thanks

Envelope><Body><APIName>
<TABLE_ID>123456 <TABLE_ID>
<ROWS>
<ROW>
<COLUMN name=""CustomerID""><![CDATA[7]]></COLUMN>
<COLUMN name=""ScoreType""><![CDATA[GENERAL]]></COLUMN>
<COLUMN name=""Score""><![CDATA[88]]></COLUMN>
</ROW>
<ROW>
<COLUMN name=""CustomerID""><![CDATA[8]]></COLUMN>
<COLUMN name=""ScoreType""><![CDATA[GENERAL]]></COLUMN>
<COLUMN name=""Score""><![CDATA[88]]></COLUMN>
</ROW>
</ROWS>
</APIName>
</Body>
</Envelope>

Replies

Russ Loski on Wed, 13 Jul 2016 17:46:56


Does this need to use the CDATA?  That is what will require Explicit mode (which most people avoid if they can).

Sam Zha on Thu, 14 Jul 2016 10:55:48


Hi Mooro1000,

Here is the complete code for this case. You may take the time to have a look.

--create the table
CREATE TABLE #T (CustomerID int, ScoreType varchar(20), Score int)
INSERT INTO #T values (7, 'General', 88)
INSERT INTO #T values (8, 'General', 88)

SELECT * FROM #T

-- output
-- 1=Body
SELECT 1 as Tag, NULL as Parent
	, NULL as [Body!1]
	, NULL as [APIName!2]
	, NULL as [TABLE_ID!3]
	, NULL as [ROWS!4]
	, NULL as [ROW!5]
	, NULL as [ROW!5!sort!HIDE] -- sort

	, NULL as [COLUMN!6!sort!HIDE] -- sort
	, NULL as [COLUMN!6!name]
	, NULL as [COLUMN!6!!CDATA]
	, NULL as [COLUMN!6!name]
	, NULL as [COLUMN!6!!CDATA]
	, NULL as [COLUMN!6!name]
	, NULL as [COLUMN!6!!CDATA]

UNION ALL
-- 2=APIName
SELECT 2, 1
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL

	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL

UNION ALL
-- 3=TABLE_ID
SELECT 3, 2
	, NULL
	, NULL
	, 123456
	, NULL
	, NULL
	, NULL

	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL

UNION ALL
-- 4=ROWS
SELECT 4, 2
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL

	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL

UNION ALL
-- 5=ROW
SELECT 5, 4
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, CustomerID

	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
FROM #T

UNION ALL
-- 6=COLUMN
SELECT 6, 5
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, CustomerID

	, 1
	, 'CustomerID'
	, CustomerID
	, NULL
	, NULL
	, NULL
	, NULL
FROM #T

UNION ALL  
SELECT 6, 5
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, CustomerID

	, 2
	, NULL
	, NULL
	, 'ScoreType'
	, ScoreType
	, NULL 
	, NULL
FROM #T

UNION ALL  
SELECT 6, 5
	, NULL
	, NULL
	, NULL
	, NULL
	, NULL
	, CustomerID

	, 3
	, NULL
	, NULL
	, NULL
	, NULL
	, 'Score' 
	, Score
FROM #T
ORDER BY [ROW!5!sort!HIDE]
	, [COLUMN!6!sort!HIDE]
FOR XML EXPLICIT, ROOT('Envelope'); -- comment this line to see the universal table