SQL Server "FOR XML" is derived from queries joining two tables
I am new to the "FOR XML" function in SQL Server. I am using SQL Server 2012.
I have two tables, Word and Word_Expansion.
Sample data:
table [Word]:
WordOID Word
------- ----
1 PIPE
2 WIRE
table [Word_Expansion]:
WEOID fk_WordOID Word_Expansion
----- ---------- --------------
1 2 COAX
2 2 SPEAKER CABLE
3 1 CONDUIT
Now I would like to create XML something like:
<expansion>
<sub>WIRE</sub>
<sub>SPEAKER CABLE</sub>
</expansion>
<expansion>
<sub>PIPE</sub>
<sub>CONDUIT</sub>
</expansion>
I've done my best to create XML FOR statements, but I just can't figure out what I need to do to get these two tables aligned to correct XML output. I'll dig further into the XML FOR syntax, but if you have a moment and know this well ...
Does anyone have any directions as to what I should try?
source to share
This should do the trick for you:
SQL:
SELECT Word Sub,
(
SELECT Word_Expansion AS Sub
FROM Word_Expansion WE
WHERE WE.fk_WordOID = W.WordOID
FOR XML PATH(''), type
)
FROM Word W
FOR XML PATH ('Expansion')
XML output:
<Expansion>
<Sub>Pipe</Sub>
<Sub>CONDUIT</Sub>
</Expansion>
<Expansion>
<Sub>Wire</Sub>
<Sub>COAX</Sub>
<Sub>SPEAKER CABLE</Sub>
</Expansion>
Although I'm not sure why you want Word.Word to be classified as "Sub"? Shouldn't that be the parent of the Word_Expansion (which should be sub?)
EDIT: I found this link quite useful when looking in FOR XML and nested queries Nested FOR XML
source to share
Here's mine ... I supported the first post because it was the fastest, but it's a little different, so I figured it wouldn't hurt to add mine ...
With groupedWords
As
(
Select WordOID,
Word
From Word
Union
Select fk_WordOID, Word_Expansion
From Word_Expansion
)
Select (Select s.word As sub
From groupedWords s
Where s.WordOID = n.WordOID
For Xml Path(''), Type)
From groupedWords n
Group By n.WordOID
For Xml Path('expansion')
source to share