SQL XML nested query

Question

How to generate below like XML based on such query?

SELECT
    t1.Code,    
    (SELECT
        CASE 
            WHEN t1.Type = 5 THEN
                (SELECT top 1
                    t2.CodeName
                FROM dbo.Table2 t2
                WHERE t2.Id = t4.Id
          FOR XML  PATH('Code'), TYPE)          
      ELSE  
          (SELECT 
             t3.CodeName
          FROM dbo.Table3 t3
                WHERE t3.Id = t4.Id
                FOR XML PATH('Code'), TYPE)                     
      END
    FROM dbo.Table4 t4
    WHERE t4.Id = t1.Id
    FOR XML PATH('CodeNames'), TYPE)    
FROM dbo.Table1  t1 WITH (NOLOCK)
FOR XML RAW ('Table1Child'), TYPE, ROOT('Table1Root')

Expected output

<Table1Root>
  <Table1Child Code="ABC">
    <CodeNames>
      <Code Name="A1" />
      <Code Name="B2" />
      <Code Name="C3" />
    </CodeNames>
  </Table1Child>
  <Table1Child Code="DEF">
    <CodeNames>
      <Code Name="D4" />
      <Code Name="E5" />
      <Code Name="F6" />
    </CodeNames>
  </Table1Child>
</Table1Root>

<Table1Root> <Table1Child Code="ABC"> <CodeNames> <Code Name="A1" /> <Code Name="B2" /> <Code Name="C3" /> </CodeNames> </Table1Child> <Table1Child Code="DEF"> <CodeNames> <Code Name="D4" /> <Code Name="E5" /> <Code Name="F6" /> </CodeNames> </Table1Child> </Table1Root> — Ostati
– Ostati, Commented Feb 8, 2012 at 0:13
please do not put code or XML into comments - it's REALLY hard to read!! Instead: update your original question by editing it, and provide that additional info. In your question, you can properly format your XML and get nice syntax highlighting, too! — marc_s
– marc_s, Commented Feb 8, 2012 at 6:07

Mikael Eriksson · Accepted Answer · 2012-02-08 06:43:49Z

1

Do a UNION instead of CASE and use @ to create an attribute.

SELECT
    t1.Code,    
    (SELECT
      (SELECT CodeName as '@Name'
       FROM (
             SELECT t2.CodeName
             FROM Table2 t2
             WHERE t2.Id = t4.Id AND
                   t1.Type = 5
             UNION ALL
             SELECT t3.CodeName
             FROM Table3 t3
             WHERE t3.Id = t4.Id AND
                   t1.Type <> 5
            ) AS T 
       FOR XML PATH('Code'), TYPE)          
     FROM Table4 t4
     WHERE t4.Id = t1.Id
     FOR XML PATH('CodeNames'), TYPE)    
FROM Table1 t1
FOR XML RAW ('Table1Child'), TYPE, ROOT('Table1Root')

It looks like you actually don't need to use Table4.

SELECT
    t1.Code,    
      (SELECT CodeName as '@Name'
       FROM (
             SELECT t2.CodeName
             FROM Table2 t2
             WHERE t2.Id = t1.Id AND
                   t1.Type = 5
             UNION ALL
             SELECT t3.CodeName
             FROM Table3 t3
             WHERE t3.Id = t1.Id AND
                   t1.Type <> 5
            ) AS T 
       FOR XML PATH('Code'), ROOT('CodeNames'), TYPE)          
FROM Table1 t1
FOR XML RAW ('Table1Child'), TYPE, ROOT('Table1Root')

answered Feb 8, 2012 at 6:43

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ostati Over a year ago

Thanks Mikael! This looks pretty neat, but I CASE is good for certain performnce issues as I'm doing some cross database lookups...

Collectives™ on Stack Overflow

SQL XML nested query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related