How to nest xml through sql query

Question

I'm trying to generate xml looking like this:

<caption nr="1000">
    <lang code="EN">Brussels</lang>
    <lang code="FR">Bruxelles</lang>
</caption>
<caption nr="2000">
    <lang code="EN">Antwerp</lang>
    <lang code="FR">Anvers</lang>
</caption>

I have all the necessary information in an sql database looking like this (sql server 2005):

fld_pcd_cod               fld_pcd_gem             fld_pcd_tal
-------------------------------------------------------------
1000                      Brussels                 EN
1000                      Bruxelles                FR
2000                      Antwerp                  EN
2000                      Anvers                   FR

I tried many sql query's with group by statements, sub queries, distintc, ... I just cant wrap my head around it.

This is the best workable solution I could get. But it's not what I want because I'd have to make a new XSD.

<lang cap="1000" code="FR">BRUXELLES</lang>
<lang cap="1000" code="EN">BRUSSELS</lang>
<lang cap="2000" code="FR">ANVERS</lang>
<lang cap="2000" code="EN">ANTWERP</lang>

This is the query I use to achieve above xml nodes

SELECT fld_pcd_cod as 'lang/@cap', fld_pcd_tal as 'lang/@code', fld_pcd_gem as 'lang'
FROM tbl_pcd
for XML path(''), type

Do you anyone has suggestions on how to solve this?

Mikael Eriksson · Accepted Answer · 2013-03-15 09:32:38Z

1

Get the captionnode with a group by on fld_pcd_cod and get lang in a correlated sub-query.

select T1.fld_pcd_cod as "@nr",
       (
       select T2.fld_pcd_tal as "@code",
              T2.fld_pcd_gem as "*"
       from tbl_pcd as T2
       where T1.fld_pcd_cod = T2.fld_pcd_cod
       for xml path('lang'), type
       )
from tbl_pcd as T1
group by T1.fld_pcd_cod
for xml path('caption')

answered Mar 15, 2013 at 9:32

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

How to nest xml through sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related