CODEWITHSUNDEEP
sql-serverxmlsql-server-2005
BBQ
BBQ

Reputation: 618

How to nest xml through sql query

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?

Upvotes: 2

Views: 65

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

Related Questions