SQL Server 2005 "FOR XML PATH" Multiple tags with same name

Question

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

Answer 1

I don't think it's possible to deal with sibling nodes with the same name using FOR XML PATH.

I was able to generate your schema using FOR XML EXPLICIT.

The output isn't valid XML as is doesn't include a definition for then xsi namespace, but it does match your spec:

create table #test
(id int identity
,name varchar(50)
,age int
,height varchar(20))

insert #test (name,age,height)
select 'Alice',32,'6 feet one inch'
union select 'Bob',30,'5 feet 10 inches'
union select 'Charles',23,'6 feet two inch'

SELECT 1         AS Tag
       ,NULL     AS Parent
       ,''       AS [root!1]
       ,null     AS [person!2!name!ELEMENT]
       ,null     AS [description!3]
       ,null     AS [description!3!xsi:type]  
       ,null     AS [description!4]
       ,null     AS [description!4!xsi:type]     
UNION ALL
SELECT 2         AS Tag
       ,1        AS Parent
       ,null
       ,name
       ,null
       ,null 
       ,null
       ,null    
FROM #test
UNION ALL
SELECT 3         AS Tag
       ,2        AS Parent
       ,null       
       ,name     
       ,age      
       ,'me:age' 
       ,null
       ,null 
FROM #test
UNION ALL
SELECT 4         AS Tag
       ,2        AS Parent
       ,null       
       ,name     
       ,null
       ,null
       ,height   
       ,'me:height'
FROM #test
order by [person!2!name!ELEMENT],Tag
FOR XML EXPLICIT

Answer 2

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>

Answer 3

We needed to produce something like this ...

<rt>
  <A>
    <B>
      <C>
        <D>
          <E>a</E>
          <E>b</E>
          <E>c</E>
          <E>d</E>
        </D>
      </C>
    </B>
  </A>
</rt>

It has repeating E elements.

We did it with the t-sql below.

select 
    '' as "A/B/C/D"
    ,
    (
        SELECT * FROM 
        (
              SELECT 'a' AS 'text()'
        UNION SELECT 'b' AS 'text()'
        UNION SELECT 'c' AS 'text()'
        UNION SELECT 'd' AS 'text()'
        ) l
        FOR XML PATH('E'),type 
    ) as 'A/B/C/D'

from [Case]
where [Case].CaseRef = 'YOUR_PKEY' 
for xml path('rt')
go 

It should be possible to poke in the xsi attributes as well ...