SQL Server “FOR XML” output from queries joining two tables
I’m new to the “FOR XML” feature in SQL Server. I am using SQL Server 2012.
I have two tables, Word and Word_Expansion.
WordOID Word ------- ---- 1 PIPE 2 WIRE
WEOID fk_WordOID Word_Expansion ----- ---------- -------------- 1 2 COAX 2 2 SPEAKER CABLE 3 1 CONDUIT
Now, I would like to produce XML something like:
<expansion> <sub>WIRE</sub> <sub>SPEAKER CABLE</sub> </expansion> <expansion> <sub>PIPE</sub> <sub>CONDUIT</sub> </expansion>
I have come close with various efforts at crafting XML FOR statements, but I just can’t seem to grasp what it is that I need to do to get these two tables mashed into the right XML output. I’ll be digging further into XML FOR syntax, but if you have a moment and know this well…
Does anyone have any pointers as to what I should try?
2 Solutions collect form web for “SQL Server “FOR XML” output from queries joining two tables”
This should do the trick for you:
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')
<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 this instead be the parent of Word_Expansion (which should be sub?)
EDIT: I found this link quite useful when looking into FOR XML and nested queries Nested FOR XML
Here is mine… I upvoted the first post because his was fastest, but it does it a little different so I figured it couldn’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')