Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem using FOR XML in SQL Query Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 3:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:07 PM
Points: 16, Visits: 131
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
Post #1469924
Posted Wednesday, July 3, 2013 3:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:58 AM
Points: 151, Visits: 648
Try this:-

SELECT 
(SELECT custname AS CustName
, custcode AS CustCode
FROM @test t1 FOR XML PATH('Item'), TYPE)
FOR XML PATH('Header'),ROOT('ns1')

Post #1469949
Posted Wednesday, July 3, 2013 7:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:07 PM
Points: 16, Visits: 131
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.
Post #1470278
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse