Problem using FOR XML in SQL Query

• spungus

  SSC-Addicted

  Points: 443

  More actions

  July 3, 2013 at 3:04 am

  #274919

  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-2 table(id int identity(1,1), custname varchar(10), custcode varchar(10) )

  insert @test-2

  values ('name1','code1'),('name2','code2')

  SELECT

  (SELECT custname,custcode FOR XML PATH(''),TYPE) AS Item

  FROM @test-2

  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

• Matthew Darwin

  SSCertifiable

  Points: 5513

  More actions

  July 3, 2013 at 3:40 am

  #1629442

  Try this:-

  SELECT

  (SELECT custname AS CustName

  , custcode AS CustCode

  FROM @test-2 t1 FOR XML PATH('Item'), TYPE)

  FOR XML PATH('Header'),ROOT('ns1')

  Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

• spungus

  SSC-Addicted

  Points: 443

  More actions

  July 3, 2013 at 7:11 pm

  #1629714

  Matthew Darwin (7/3/2013)

  ---

  Try this:-

  SELECT

  (SELECT custname AS CustName

  , custcode AS CustCode

  FROM @test-2 t1 FOR XML PATH('Item'), TYPE)

  FOR XML PATH('Header'),ROOT('ns1')

  Thanks Matthew Darwin! i see how it is construct.