SQL Server for xml returns custom xml format?

Question

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?

marc_s · Accepted Answer · 2013-03-21 18:21:07Z

6

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

edited Mar 21, 2013 at 18:21

answered Mar 21, 2013 at 17:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vasant · Accepted Answer · 2013-03-21 19:41:38Z

1

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)

answered Mar 21, 2013 at 19:41

Vasant

3014 silver badges14 bronze badges

Comments

Mikael Eriksson · Accepted Answer · 2013-03-21 18:22:53Z

0

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.

edited Mar 21, 2013 at 18:22

answered Mar 21, 2013 at 17:58

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

SQL Server for xml returns custom xml format?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related