Xml output in SQL Server

Question

I want table output as XML. My table result is

I need result like

Query

Declare @colorTable table (Category varchar(100),Attribute varchar(100))
insert into @colorTable values ('Color','Red')
insert into @colorTable values ('Color','Blue')
insert into @colorTable values ('Color','Green')
insert into @colorTable values ('Transport','Bus')
insert into @colorTable values ('Transport','Car')
insert into @colorTable values ('Transport','Twoweeler')

select * from @colorTable
FOR XML PAth(''), ROOT ('xml'), ELEMENTS;

Thanks,

S.Sundar

Have you tried anything so far? For example, using select ... for xml. — Andrey Korneyev
– Andrey Korneyev, Commented Sep 2, 2015 at 14:56
If you will show some of your attempts then someone could point to the errors you've made. Otherwise it looks like "please write code for me" request. — Andrey Korneyev
– Andrey Korneyev, Commented Sep 2, 2015 at 15:04
I tried like this Declare @colorTable table (Category varchar(100),Attribute varchar(100)) insert into @colorTable values ('Color','Red') insert into @colorTable values ('Color','Blue') insert into @colorTable values ('Color','Green') insert into @colorTable values ('Transport','Bus') insert into @colorTable values ('Transport','Car') insert into @colorTable values ('Transport','Twoweeler') select * from @colorTable FOR XML PAth(''), ROOT ('xml'), ELEMENTS; — user2354274
– user2354274, Commented Sep 2, 2015 at 15:31

wiretext · Accepted Answer · 2015-09-02 16:15:47Z

we have to write a query using GROUP BY, XML path not allowed to write sql within xml path(not allowed select statement)

Declare @colorTable table (Category varchar(100),Attribute varchar(100))
insert into @colorTable values ('Color','Red')
insert into @colorTable values ('Color','Blue')
insert into @colorTable values ('Color','Green')
insert into @colorTable values ('Transport','Bus')
insert into @colorTable values ('Transport','Car')
insert into @colorTable values ('Transport','Twoweeler')

select T1.Category as '@Value',
       (
       select T2.Attribute as '@Value'
       from @colorTable as T2
       where T2.Category = T1.Category
       group by T2.Attribute
       for xml path('Attribute'), type
       )
from @colorTable as T1
group by Category
for xml path('Category'), root('xml')

OUTPUT

<xml>
  <Category Value="Color">
    <Attribute Value="Blue" />
    <Attribute Value="Green" />
    <Attribute Value="Red" />
  </Category>
  <Category Value="Transport">
    <Attribute Value="Bus" />
    <Attribute Value="Car" />
    <Attribute Value="Twoweeler" />
  </Category>
</xml>

Andrey Korneyev · Accepted Answer · 2015-09-02 15:37:13Z

0

Well, in your particular case to achieve your goal you should made three subselects for your three subbranches Color, Transport and Electronics and then join that xml parts under xml root.

You can do it using following query:

select
    (
        select
            Attribute as 'attribute/@value'
        from @colorTable
        where Category = 'Color'
        for xml path(''), type
    ) as Color,
    (
        select
            Attribute as 'attribute/@value'
        from @colorTable
        where Category = 'Transports'
        for xml path(''), type
    ) as Transports,
    (
        select
            Attribute as 'attribute/@value'
        from @colorTable
        where Category = 'Electronics'
        for xml path(''), type
    ) as Electronics
for xml path(''), root('xml')

answered Sep 2, 2015 at 15:37

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

2 Comments

user2354274 Over a year ago

color , transport will come dynamically. that is not static

Andrey Korneyev Over a year ago

@user2354274 Having dynamic element names in your xml looks like a bad idea. So if it should be dynamic, consider changing your xml structure from <Color>...</Color> to <Category name="Color">...</Category>.

Daniel Stackenland · Accepted Answer · 2015-09-02 15:37:41Z

0

Take a look at EXPLICIT Mode with FOR XML MSDN. It can be a pain , but you can control the output ;-)

answered Sep 2, 2015 at 15:37

Daniel Stackenland

3,2591 gold badge21 silver badges23 bronze badges

Comments

user2354274 · Accepted Answer · 2015-09-02 16:19:26Z

0

Finally I put code like this

Select Category as value , (SELECT Attribute as value from @colorTable where Category= a.Category
FOR XML raw('attribute'), TYPE) from @colorTable as a group by Category FOR XML raw('category'), ROOT ('xml'), type;

answered Sep 2, 2015 at 16:19

user2354274

1875 silver badges16 bronze badges

Collectives™ on Stack Overflow

Xml output in SQL Server

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related