July 3, 2013 at 3:04 am
Hi to all,
I have one format problem that i cannot get it right. I hope you can help me.
So here it is.
declare @test table(id int identity(1,1), custname varchar(10), custcode varchar(10) )
insert @test
values ('name1','code1'),('name2','code2')
SELECT
(SELECT custname,custcode FOR XML PATH(''),TYPE) AS Item
FROM @test
FOR XML PATH('Header'),ROOT('ns1')
The above code it will output like this.
<ns1>
<Header>
<Item>
<custname>name1</custname>
<custcode>code1</custcode>
</Item>
</Header>
<Header>
<Item>
<custname>name2</custname>
<custcode>code2</custcode>
</Item>
</Header>
</ns1>
What i want is something like this...
<ns1>
<Header>
<Item>
<custname>name1</custname>
<custcode>code1</custcode>
</Item>
<Item>
<custname>name2</custname>
<custcode>code2</custcode>
</Item>
</Header>
</ns1>
All of your answers is very much appreciated. thanks
July 3, 2013 at 3:40 am
Try this:-
SELECT
(SELECT custname AS CustName
, custcode AS CustCode
FROM @test t1 FOR XML PATH('Item'), TYPE)
FOR XML PATH('Header'),ROOT('ns1')
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
July 3, 2013 at 7:11 pm
Matthew Darwin (7/3/2013)
Try this:-
SELECT(SELECT custname AS CustName
, custcode AS CustCode
FROM @test t1 FOR XML PATH('Item'), TYPE)
FOR XML PATH('Header'),ROOT('ns1')
Thanks Matthew Darwin! i see how it is construct.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply