SQL Server 2005 & ldquo; FOR XML PATH & rdquo; Several labels with the same name

advertisements

I have an XML structure like the following

<root>
    <person>
        <name>James</name>
        <description xsi:type="me:age">12</description>
        <description xsi:type="me:height>6 foot</description>
...

Which I have to pull out of a table like ...

Person

Name , Age , Height

I'm trying to use the FOR XML path stuff in SQL 2005 with a query like

SELECT
Name as 'name'
Age as 'description xsi:type="me:age"'
Height as 'description xsi:type="me:height"'
FOR XML PATH('person')

But it gives me an error about the 'description xsi' namespace being missing. Is there any way to achieve this using FOR XML PATH. The actual query is rather more complex than this example and would take a lot of effort to change.

Thanks


FOR XML PATH is a little difficult at times (at least from what I know). This may get you there:

WITH XMLNAMESPACES('uri' as xsi)
SELECT
'me:age'     AS 'description/@xsi:type'
,age         AS 'description'
,name        AS 'name'
,'me:height' AS 'description/@xsi:type'
,height      AS 'description'
FROM #test
FOR XML PATH('person')

Produces:

<person xmlns:xsi="uri">
  <description xsi:type="me:age">32</description>
  <name>Alice</name>
  <description xsi:type="me:height">6 Foot</description>
</person>
<person xmlns:xsi="uri">
  <description xsi:type="me:age">24</description>
  <name>Bob</name>
  <description xsi:type="me:height">5 Feet 5 Inches</description>
</person>