Need help with "for xml" output structure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with "for xml" output structure

Author

Topic

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-11-12 : 15:17:12

I am trying to get a dynamic out put based on the data in a table for XML.

Any help would be greatly appreciated!

here is a sample of data.....

create Table #My_Table
(PCNumber varchar(50),
SectionID varchar(10),
Elementcode varchar(10),
Atext varchar(200),
ADate datetime)

Insert into #My_Table Values ('2007-564','E02','E02_01','Alarm','11/11/2007 09:01:05 AM')
Insert into #My_Table Values ('2007-564','E02','E02_04','911','11/11/2007 09:01:45 AM')
Insert into #My_Table Values ('2007-564','E02','E02_05','Call Back','11/11/2007 09:03:15 AM')
Insert into #My_Table Values ('2007-564','E02','E02_06','No response','11/11/2007 09:10:36 AM')
Insert into #My_Table Values ('2007-564','E03','E03_01','Keyboard','11/11/2007 09:12:54 AM')
Insert into #My_Table Values ('2007-564','E03','E03_02','Monitor','11/11/2007 09:18:12 AM')
Insert into #My_Table Values ('2007-564','E03','E03_03','Lan wire','11/11/2007 09:18:26 AM')
Insert into #My_Table Values ('2007-564','E04','E04_01','Chris','11/11/2007 09:22:15 AM')
Insert into #My_Table Values ('2007-564','E04','E04_02','Any street','11/11/2007 09:22:15 AM')
Insert into #My_Table Values ('2007-564','E04','E04_03','Any town','11/11/2007 09:22:15 AM')
Insert into #My_Table Values ('2007-564','E04','E04_04','zip','11/11/2007 09:22:15 AM')
Insert into #My_Table Values ('2007-564','E04','E04_01','Chris','11/11/2007 09:29:56 AM')
Insert into #My_Table Values ('2007-564','E04','E04_02','ship street','11/11/2007 09:29:56 AM')
Insert into #My_Table Values ('2007-564','E04','E04_03','Ship town','11/11/2007 09:29:56 AM')
Insert into #My_Table Values ('2007-564','E04','E04_04','Ship zip','11/11/2007 09:29:56 AM')
Insert into #My_Table Values ('2007-564','E05','E05_02','Collection','11/11/2007 09:04:22 AM')
Insert into #My_Table Values ('2007-564','E05','E05_03','Company','11/11/2007 09:34:12 AM')
Insert into #My_Table Values ('2007-564','E05','E05_04','Bank Name','11/11/2007 09:32:42 AM')
Insert into #My_Table Values ('2007-564','E05','E05_07','bank phonenumber','11/11/2007 09:23:18 AM')

I need the output to be in XML. I am having issues with the structure using Sql 2005.

Here is what the above data should look like in XML.
<PC>
<PCnumber>
<ID>2007-564</ID>
<E02>
<E02_01>Alarm</E02_01>
<E02_04>911</E02_04>
<E02_05>Call Back</E02_05>
<E02_06>No Response</E02_06>
</E02>
<E03>
<E03_01>Keyboard</E03_01>
<E03_02>Monitor</E03_02>
<E03_03>Lan wire</E03_03>
</E03>
</E04>
<E04_01>Chris</E04_01>
<E04_02>Any Street</E04_02>
<E04_03>Any Town</E04_03>
<E04_04>zip</E04_04>
<E04_01>Chris</E04_01>
<E04_02>ship Street</E04_02>
<E04_03>ship Town</E04_03>
<E04_04>ship zip</E04_04>
</E04>
<E05>
<E05_02>Collection</E05_02>
<E05_03>Company</E05_03>
<E05_04>Bank Name</E05_04>
<E05_07>Bank phonenumber</E05_07>
</E05>
</PCnumber>
</PC>

OK.... Granted I am not an XML guy, but I think you can get the point from the above sample.

On the E04 section, if you notice in the table the Dates are exactly the same, that is how you know those go together with each other.
I have tried many different ways using For XML EXplicit, auto, raw and path, but have not had much luck. I believe mainly due to lack of experience using the for xml.
Each PCnumber tag should be for every unique PCnumber.

Thank you for any help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 15:46:23

That's one ugly looking XML file.
Are you really sure you want the "XML" file to look like this?

E 12°55'05.25"
N 56°04'39.16"

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-11-12 : 15:55:16

So my xml is lacking seriously!
What I need is an xml that returns on set for each unique PCnumber. I need the tags to follow the Elementcode column and display the atext but grouped by the SectionID column.
It doesn't have to be that exact format by any means, but I put that out there so you can see the structure flow per say.

I hope that makes sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 17:04:38

[code]set nocount on

DECLARE @Output TABLE (RowID INT IDENTITY(0, 1), Col1 VARCHAR(200), Col2 VARCHAR(200), Col3 VARCHAR(200), Yak TINYINT, Indent TINYINT)

INSERT @Output (Col1, Col2, Col3, Yak, Indent)
select '<ID>' + PCNumber + '</ID>',
'<' + SectionID + '>',
'<' + ElementCode + '>' + aText + '</' + ElementCode + '>',
10,
3
from #My_Table

union all

select '<ID>' + PCNumber + '</ID>',
'<' + SectionID + '>',
'<' + SectionID + '>',
9,
2
from #My_Table

union all

select '<ID>' + PCNumber + '</ID>',
'<' + SectionID + '>',
'</' + SectionID + '>',
11,
2
from #My_Table

union all

select '<ID>' + PCNumber + '</ID>',
'<' + SectionID + '>',
'<ID>' + PCNumber + '</ID>',
8,
2
from #My_Table

union all

select '<ID>' + PCNumber + '</ID>',
'<' + SectionID + '>',
'<PCnumber>',
7,
1
from #My_Table

union all

select '<ID>' + PCNumber + '</ID>',
'<' + SectionID + '>',
'</PCnumber>',
12,
1
from #My_Table

order by 1, 2, 4, 3

DELETE o
FROM @Output AS o
INNER JOIN (
SELECT Col1,
Yak,
MIN(RowID) AS mi,
MAX(RowID) AS ma
FROM @Output
WHERE Yak IN (7, 8)
GROUP BY Col1,
Yak
) AS e ON e.Col1 = o.Col1
AND e.Yak = o.Yak
WHERE o.RowID > e.mi
AND o.RowID <= e.ma

DELETE o
FROM @Output AS o
INNER JOIN (
SELECT Col1,
Yak,
MIN(RowID) AS mi,
MAX(RowID) AS ma
FROM @Output
WHERE Yak = 12
GROUP BY Col1,
Yak
) AS e ON e.Col1 = o.Col1
AND e.Yak = o.Yak
WHERE o.RowID >= e.mi
AND o.RowID < e.ma

DELETE o
FROM @Output AS o
INNER JOIN (
SELECT Col1,
Col2,
Yak,
MIN(RowID) AS mi,
MAX(RowID) AS ma
FROM @Output
WHERE Yak = 9
GROUP BY Col1,
Col2,
Yak
) AS e ON e.Col1 = o.Col1
AND e.Col2 = o.Col2
AND e.Yak = o.Yak
WHERE o.RowID > e.mi
AND o.RowID <= e.ma

DELETE o
FROM @Output AS o
INNER JOIN (
SELECT Col1,
Col2,
Yak,
MIN(RowID) AS mi,
MAX(RowID) AS ma
FROM @Output
WHERE Yak = 11
GROUP BY Col1,
Col2,
Yak
) AS e ON e.Col1 = o.Col1
AND e.Col2 = o.Col2
AND e.Yak = o.Yak
WHERE o.RowID >= e.mi
AND o.RowID < e.ma

DECLARE @XML VARCHAR(MAX)

SET @XML = '<PC>'

SELECT @XML = @XML + d.Dat
FROM (
SELECT TOP 100 PERCENT
CHAR(13) + CHAR(10) + REPLICATE(CHAR(9), Indent) + Col3 AS Dat
FROM @Output
ORDER BY RowID
) AS d

SET @XML = @XML + CHAR(13) + CHAR(10) + '</PC>'

print @xml
select @xml[/code]

E 12°55'05.25"
N 56°04'39.16"

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-11-12 : 18:07:16

that made it exactly like the sample data.
very nice!

seeing that my xml skills are lacking.. What would you recommend?
is the way you just gave the best? or are there better ways possibly?
Everything I did using the for xml, gave me several sets of xml instead of one set. I am trying to understand the "for xml" stuff better. Is there a way to return the data in the Atext using the ElementCode as the Tag and the SectionID as the loop? I tried using pivot table command in the for xml and stuff like that, but wasn't completely successful. The closes I was is when I used for xml path and defined the paths, but still wasn't what I needed and returned several xml sets.

thank you in advance for any assistance you can give in my learning process!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 18:34:22

You realize I had to do this way becuase there is no way [that I know of] to build the XML string the way you want with the sample data you provided?

E 12°55'05.25"
N 56°04'39.16"

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-11-12 : 18:38:54

That looks like it would work too!

Now how do I do that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 19:25:14

[code]DECLARE @My_Table TABLE
(
PCNumber varchar(50),
SectionID varchar(10),
Elementcode varchar(10),
Atext varchar(200),
ADate datetime)

Insert into @My_Table Values ('2007-564','E02','E02_01','Alarm','11/11/2007 09:01:05 AM')
Insert into @My_Table Values ('2007-564','E02','E02_04','911','11/11/2007 09:01:45 AM')
Insert into @My_Table Values ('2007-564','E02','E02_05','Call Back','11/11/2007 09:03:15 AM')
Insert into @My_Table Values ('2007-564','E02','E02_06','No response','11/11/2007 09:10:36 AM')
Insert into @My_Table Values ('2007-564','E03','E03_01','Keyboard','11/11/2007 09:12:54 AM')
Insert into @My_Table Values ('2007-564','E03','E03_02','Monitor','11/11/2007 09:18:12 AM')
Insert into @My_Table Values ('2007-564','E03','E03_03','Lan wire','11/11/2007 09:18:26 AM')
Insert into @My_Table Values ('2007-564','E04','E04_01','Chris','11/11/2007 09:22:15 AM')
Insert into @My_Table Values ('2007-564','E04','E04_02','Any street','11/11/2007 09:22:15 AM')
Insert into @My_Table Values ('2007-564','E04','E04_03','Any town','11/11/2007 09:22:15 AM')
Insert into @My_Table Values ('2007-564','E04','E04_04','zip','11/11/2007 09:22:15 AM')
Insert into @My_Table Values ('2007-564','E04','E04_01','Chris','11/11/2007 09:29:56 AM')
Insert into @My_Table Values ('2007-564','E04','E04_02','ship street','11/11/2007 09:29:56 AM')
Insert into @My_Table Values ('2007-564','E04','E04_03','Ship town','11/11/2007 09:29:56 AM')
Insert into @My_Table Values ('2007-564','E04','E04_04','Ship zip','11/11/2007 09:29:56 AM')
Insert into @My_Table Values ('2007-564','E05','E05_02','Collection','11/11/2007 09:04:22 AM')
Insert into @My_Table Values ('2007-564','E05','E05_03','Company','11/11/2007 09:34:12 AM')
Insert into @My_Table Values ('2007-564','E05','E05_04','Bank Name','11/11/2007 09:32:42 AM')
Insert into @My_Table Values ('2007-564','E05','E05_07','bank phonenumber','11/11/2007 09:23:18 AM')

SELECT DISTINCT 1 AS Tag,
NULL AS Parent,
PCNumber AS [PCnumber!1!ID],
NULL AS [Info!2!Category],
NULL AS [Info!2!Type],
NULL AS [Info!2]
FROM @My_Table

UNION ALL

SELECT DISTINCT 2 AS Tag,
1 AS Parent,
PCNumber,
SectionID,
ElementCode,
aText
FROM @My_Table

FOR XML EXPLICIT, ROOT('PC')[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 16:21:14

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93761

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources