CODEWITHSUNDEEP
sql-serverxmlsql-server-2008xquery
ca9163d9
ca9163d9

Reputation: 29159

SQL Server for xml returns custom xml format?

I have the following SQL 

with X(x) as (select 'a' union select 'b' )
   , Y(y) as (select 1 union select 2 union select 3)
select x, y from X cross join Y
for xml path('')

and it returns

<x>a</x>
<y>1</y>
<x>a</x>
<y>2</y>
<x>a</x>
<y>3</y>
<x>b</x>
<y>1</y>
<x>b</x>
<y>2</y>
<x>b</x>
<y>3</y>

Is it possible to make it returns

<a><y>1</y><y>2</y><y>3</y></a>
<b><y>1</y><y>2</y><y>3</y></b>

or

<x v="a"><y>1</y><y>2</y><y>3</y></x>
<x v="b"><y>1</y><y>2</y><y>3</y></x>

Or maybe I should do the permutation using xquery FLWOR?

Upvotes: 0

Views: 314

Answers (3)

Vasant
Vasant

Reputation: 301

You can try this to get your first resultset

create table #t1(id int)

insert into #t1
select 1 union select 2 union select 3

create table #row(ch char(1))
insert into #row
select 'a' union select 'b'

select cast ((
select(
select

'<' + cast(ch as varchar(max))  + '>',
(
select(
CAST(
(select id as 'Y' from #t1
for xml path(''), type
)as varchar(max)
)
)
)
,
'</' + cast(ch as varchar(max))  + '>'
from #row
for xml path(''),root('root'), type
).value('/root[1]','varchar(max)')
) as xml)

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Here a query for the first result.

select cast('<'+x+'>' + 
               (
               select y 
               from Y 
               for xml path('')
               ) + 
             '</'+x+'>' 
       as xml) 
from X
for xml path('')

It is a bit risky because all values in x has to be valid XML element names.

Upvotes: 0

marc_s
marc_s

Reputation: 754268

If you use this:

WITH X(x) AS (SELECT 'a' UNION SELECT 'b' )
   , Y(y) AS (SELECT 1 UNION SELECT 2 UNION SELECT 3)
SELECT
    x AS "@v",
    (SELECT y FROM Y 
     FOR XML PATH(''), TYPE)
FROM X 
FOR XML PATH('X')

you should get an output of:

<X v="a">
  <y>1</y>
  <y>2</y>
  <y>3</y>
</X>
<X v="b">
  <y>1</y>
  <y>2</y>
  <y>3</y>
</X>

I don't know of any way to use the value of a column (a) as a XML tag in your XML output ( <a>......</a>).

Upvotes: 6

Related Questions