Help generating XML from relational data

Question

Help generating XML from relational data

brent.baker

SSC Rookie

Points: 27
More actions
May 14, 2010 at 12:11 pm

#100726

I have to deliver an XML payload into a collumn in a predefined format from a system that I axminster. I need to put the results from this query:
SELECT Header.id as [HeaderID]
, Detail.detailID as [ID]
, Detail.detailAttribute1 as [attribute1]
, Detail.detailAttribute2 as [attribute2]
, Detail.detailAddtribute3 as [attribute3]
, Header.headerAttribute1 as [headerAttribute1]
FROM table1 Header
JOIN table2 Detail
ON Header.HeaderID = Detail.SomeCollumn
WHERE yada = yada
In to this XML structure
]<RootName>
<HeaderID>1234</HeaderID>
<Detail>
<Detail ID="123xyz" attribute1="11111" attribute2="abcd" attribute3="22.11" />
<Detail ID="124xyz" attribute1="11112" attribute2="abce" attribute3="22.12" />
</Detail>
<headerAttribute1>false</headerAttribute1>
</RootName>
Using the FOR XML .... I have gotten close but not quite to the specified format. Can anyone help?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would you please provide table definition and some sample data in a ready to use format as described in the first link in my signature?

By doing so you would give us something to test against and in return you'll get tested code.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

brent.baker SSC Rookie Points: 27 More actions · Answer 2

lmu92 (5/14/2010)
Would you please provide table definition and some sample data in a ready to use format as described in the first link in my signature?
By doing so you would give us something to test against and in return you'll get tested code.

Awesome! Hopefully this has everything included.

--===== If the Detail table already exists, drop it

IF OBJECT_ID('TempDB..#Detail','U') IS NOT NULL DROP TABLE #Detail

--===== Create the Detail table with

CREATE TABLE #Detail(

detailID varchar(25) NOT NULL,

HeaderID bigint NULL,

detailAttribute1 bigint NULL,

detailAttribute2 float NULL,

detailAddtribute3 varchar(17) NULL)

--===== If the Header table already exists, drop it

IF OBJECT_ID('TempDB..#Header','U') IS NOT NULL DROP TABLE #Header

--===== Create the Header table with

CREATE TABLE #Header(

id bigint NOT NULL,

headerAttribute1 bit NULL )

--===== Insert the Detail data

INSERT INTO #Detail(

DetailID,

HeaderID, -- This is the foreign key to the header table

detailAttribute1,

detailAttribute2,

detailAddtribute3)

SELECT '1023100954260092570012300','384153','310095','42.6','10231009509257' UNION ALL

SELECT '1023100954315092570012400','384153','310095','43.15','10231009509257' UNION ALL

SELECT '1023100954275092570012600','384153','310095','42.75','10231009509257' UNION ALL

SELECT '1023100954355092570012700','384153','310095','43.55','10231009509257' UNION ALL

SELECT '1023100954305092570012800','384153','310095','43.05','10231009509257' UNION ALL

SELECT '1023100954190092570012900','384153','310095','41.9','10231009509257' UNION ALL

SELECT '1023100954245092570013000','384153','310095','42.45','10231009509257' UNION ALL

SELECT '1023100954200092570013100','384153','310095','42','10231009509257' UNION ALL

SELECT '1023100954270092570013200','384153','310095','42.7','10231009509257' UNION ALL

SELECT '1023100954270092570013300','384153','310095','42.7','10231009509257' UNION ALL

SELECT '1023100954145092570013400','384153','310095','41.45','10231009509257' UNION ALL

SELECT '1023100954355092570013500','384153','310095','43.55','10231009509257' UNION ALL

SELECT '1023100954255092570013600','384153','310095','42.55','10231009509257' UNION ALL

SELECT '1023100954310092570013700','384153','310095','43.1','10231009509257' UNION ALL

SELECT '1023100954345092570013800','384153','310095','43.45','10231009509257'

--===== Insert the Header data

INSERT INTO #Header(ID,headerAttribute1)

SELECT '384153','0'

-- This is the modified T_SQL query I am trying to produce the XML with

SELECT Header.id as [HeaderID]

, Detail.detailID as [ID]

, Detail.detailAttribute1 as [attribute1]

, Detail.detailAttribute2 as [attribute2]

, Detail.detailAddtribute3 as [attribute3]

, Header.headerAttribute1 as [headerAttribute1]

FROM #Header Header

JOIN #Detail Detail

ON Header.ID = Detail.HeaderID

Let me know if I have left anything out. The help is greatly appreciated.

LutzM SSC Guru Points: 107049 More actions · Answer 3

First of all: THANK YOU for providing perfectly formatted test data! Awesome job! 😀

And here's what I came up with (as far as I can se it'll return your expected result):

SELECT

1 AS Tag,

NULL AS Parent,

Header.id AS 'HeaderID!1',

NULL AS 'Details!2',

NULL AS 'Detail!3!ID',

NULL AS 'Detail!3!attribute1',

NULL AS 'Detail!3!attribute2',

NULL AS 'Detail!3!attribute3',

NULL AS 'HeaderAttribute1!4'

FROM #Header Header

UNION ALL

SELECT

2 AS Tag,

NULL AS Parent,

NULL AS 'HeaderID!1',

NULL AS 'Details!2',

NULL AS 'Detail!3!ID',

NULL AS 'Detail!3!attribute1',

NULL AS 'Detail!3!attribute2',

NULL AS 'Detail!3!attribute3',

NULL AS 'HeaderAttribute1!4'

UNION ALL

SELECT

3 AS Tag,

2 AS Parent,

NULL AS 'HeaderID!1',

NULL AS 'Details!2',

Detail.detailID AS 'Detail!3!ID',

Detail.detailAttribute1 AS 'Detail!3!attribute1',

Detail.detailAttribute2 AS 'Detail!3!attribute2',

Detail.detailAddtribute3 AS 'Detail!3!attribute3',

NULL AS 'HeaderAttribute1!4'

FROM #Header Header

JOIN #Detail Detail

ON Header.ID = Detail.HeaderID

UNION ALL

SELECT

4 AS Tag,

NULL AS Parent,

NULL AS 'HeaderID!1',

NULL AS 'Details!2',

NULL AS 'Detail!3!ID',

NULL AS 'Detail!3!attribute1',

NULL AS 'Detail!3!attribute2',

NULL AS 'Detail!3!attribute3',

CASE WHEN Header.headerAttribute1 = 0 THEN 'false' ELSE 'true' END AS 'HeaderAttribute1!4'

FROM #Header Header

FOR XML EXPLICIT, ROOT('RootName')

Side note: a great resource when dealing with XML stuff is Jacob Sebastians blog. (I had to use it, too in order to get the result you requested, since I don't use FOR XML EXPLICIT that often... 😉

Edit: If you need a more detailed explanation how the code above works, let me know.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

brent.baker SSC Rookie Points: 27 More actions · Answer 4

Thank You!

Seeing the code helps me understand it much better now.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

By the way - you didn't mention the version of SQL Server you're using (it makes a difference).

For example - if you are using SQL 2005 or higher, you can use the FOR XML PATH, which is quite a bit simpler...

SELECT Header.id as "HeaderID"

, (Select Detail.detailID as "@ID"

, Detail.detailAttribute1 as "@attribute1"

, Detail.detailAttribute2 as "@attribute2"

, Detail.detailAddtribute3 as "@attribute3"

From table2 Detail

where Header.HeaderID = Detail.SomeCollumn

FOR XML PATH('detail'), Root('detail'), TYPE)

, Header.headerAttribute1 as "headerAttribute1"

FROM table1 Header

FOR XML PATH('RootName')

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

LutzM SSC Guru Points: 107049 More actions · Answer 6

Nice solution, Matt!!

Much cleaner code thant the FOR XML EXPLICIT stuff...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Yes - for XML PATH is very nice, especially with complex formats. With EXPLICIT set up as a UNION (and every node and level needing to be defined), PATH will let you define file formats that EXPLICIT won't.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?