FOR XML - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

FOR XML

Author

Topic

parody
Posting Yak Master

111 Posts

Posted - 2011-07-21 : 06:35:56

OK, so I'm not an expert with for xml, I need to format the following sample data

Ref Letter
1 a
1 b
1 c
2 a
2 b
3 a
4 a
4 b

To look comething like this, i.e. to array the occurences of Letter under the reference. I've been fiddling with PATH but can't quite get it right.

<TableName>
<Ref=1>
<Letter>a</Letter>
<Letter>b</Letter>
<Letter>c</Letter>
</Ref>
<Ref=2>
<Letter>a</Letter>
<Letter>b</Letter>
</Ref>
<Ref=3>
<Letter>a</Letter>
</Ref>
<Ref=4>
<Letter>a</Letter>
<Letter>b</Letter>
</Ref>
</TableName>

parody
Posting Yak Master

111 Posts

Posted - 2011-07-21 : 06:39:15

Ah I think I just worked it out, need to nest the letters in another FOR XML...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-21 : 06:43:03

That is not even valid XML. You can't have an attribute only in an element.

N 56°04'39.26"
E 12°55'05.63"

parody
Posting Yak Master

111 Posts

Posted - 2011-07-21 : 07:03:33

Sorry, quick post, Ref should be attribute of table element.

I've done it now anyway!

Thanks

parody
Posting Yak Master

111 Posts

Posted - 2011-07-21 : 07:45:43

Doh I mean letters element, so final result is:

<TableName>
<Letters Ref=1>
<Letter>a</Letter>
<Letter>b</Letter>
<Letter>c</Letter>
</Letters>
<Letters Ref=2>
<Letter>a</Letter>
<Letter>b</Letter>
</Letters>
<Letters Ref=3>
<Letter>a</Letter>
</Letters>
<Letters Ref=4>
<Letter>a</Letter>
<Letter>b</Letter>
</Letters>
</TableName>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-21 : 07:57:38

[code]SELECT s.Ref AS [@Ref],
(
SELECT x.Letter
FROM @Sample AS x
WHERE x.Ref = s.Ref
ORDER BY x.Letter
FOR XML PATH(''),
TYPE
)
FROM @Sample AS s
GROUP BY s.Ref
ORDER BY s.Ref
FOR XML PATH('Letters'),
ROOT('TableName')[/code]

N 56°04'39.26"
E 12°55'05.63"

parody
Posting Yak Master

111 Posts

Posted - 2011-07-21 : 08:18:29

Indeed, this is what I ended up with (with sudo naming in place), which is near identical. It was the PATH('') that was stuffing me up on the subquery, i was putting PATH('blah'), and aliasing the subquery.

SELECT
Ref AS "@Ref"
,(SELECT Letter FROM @Letters L2 WHERE L1.Ref= L2.Ref FOR XML PATH(''),type)
FROM @Letters L1
GROUP BY Ref
FOR XML PATH('Letters'),ROOT('TableName')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources