SQL Server 2005 "FOR XML PATH" Multiple Tags with the Same Name

Question

I have an XML structure such as

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

What do I need to pull from the table, how ...

Person

Name, Age, Height

I am trying to use FOR XML path material 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 this gives me the error that the namespace 'description xsi' is missing. Is there any way to achieve this using FOR XML PATH. The actual query is more complex than this example, and it will take a lot of effort to change.

thanks

Answer 1

FOR XML PATH is a bit more complicated (at least from what I know). This may lead 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')

It 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 2

, - , FOR XML PATH.

FOR XML EXPLICIT.

. XML- xsi, :

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 3

, eddiegroves ', , .

( eddiegroves):

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

Answer 4

<rt> 
  <A> 
    <B> 
      <C> 
        <D> 
          <E>a</E> 
          <F>b</F> 
          <G>c</G> 
        </D> 
    <D> 
          <E>x</E> 
          <F>y</F> 
          <G>z</G> 
        </D> 
      </C> 
    </B> 
  </A> 
</rt> 

- ,

,(SELECT * FROM          
    (SELECT '' AS 'text()'           
    ) l         
    FOR XML PATH(''),type) as 'A/B/C' 

-

Answer 5

- ...

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

E.

t-sql .

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 

xsi