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.

  • Generate an Update SQL for Changing the Order of an Item?
  • Use SqlConnection.GetSchema to get Tables Only (No Views)
  • How select data where they have on second difference?
  • Create a nullable column using SQL Server SELECT INTO?
  • Implementing transactions over multiple databases
  • 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 produce XML something like:

      <sub>SPEAKER CABLE</sub>

    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')   

    XML Output:

      <Sub>SPEAKER CABLE</Sub>

    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
            Select  WordOID, 
            From    Word
            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')
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.