Generate XML From SQL Query

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  May 5, 2014 at 8:30 am

  #396706

  My client requirement is to generate XML From Two Tables and make hierarchy with same table at same level. I am posting test Data when you will run the query below it Does not nest Detail Addresses in Detail Tag.

  Declare @SaoHeader AS Table (SAOID int,SAODate DateTime,City varchar(50),State varchar(50))

  insert into @SaoHeader(SAOID ,SAODate ,City ,State )Values(1,getdate(),'abc','xyz')

  insert into @SaoHeader(SAOID ,SAODate ,City ,State )Values(2,getdate(),'cef','ghi')

  Declare @SaoHeaderDetail AS Table (SAOID int,SAODetID int,productID int,shipAddr varchar(50),BillAddr varchar(50))

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(1,1,1,'abc','xyz')

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(1,2,2,'lep','mno')

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(2,1,1,'fre','hjj')

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(2,2,2,'der','hyi')

  select H.SAOID

  ,H.SAODate

  ,H.City "HeaderAddress/City" --Header Address New Tag

  ,H.State "HeaderAddress/State" --Header Address New Tag

  ,Det.SAODetID "Detail/SAODetID" --Detail Info New Tag

  ,Det.productID "Detail/productID" --Detail Info New Tag

  ,Det.BillAddr "DetailAddresses/BillAddr" --Detail Address New TAg

  ,Det.shipAddr "DetailAddresses/shipAddr" --Detail Address New TAg

  from @SaoHeader H

  inner join @SaoHeaderDetail det on H.SAOID=det.SAOID

  for XML PAth('SalesHeader'),Elements,Root('Sales')

  I want result of query as below

  <Sales>

  <SalesHeader>

  <SAOID>1</SAOID>

  <SAODate>2014-05-05T19:25:25.583</SAODate>

  <HeaderAddress>

  <City>abc</City>

  <State>xyz</State>

  </HeaderAddress>

  <Detail>

  <SAODetID>1</SAODetID>

  <productID>1</productID>

  <DetailAddresses>

  <BillAddr>xyz</BillAddr>

  <shipAddr>abc</shipAddr>

  </DetailAddresses>

  </Detail>

  </SalesHeader>

  <SalesHeader>

  <SAOID>1</SAOID>

  <SAODate>2014-05-05T19:25:25.583</SAODate>

  <HeaderAddress>

  <City>abc</City>

  <State>xyz</State>

  </HeaderAddress>

  <Detail>

  <SAODetID>2</SAODetID>

  <productID>2</productID>

  <DetailAddresses>

  <BillAddr>mno</BillAddr>

  <shipAddr>lep</shipAddr>

  </DetailAddresses>

  </Detail>

  </SalesHeader>

  <SalesHeader>

  <SAOID>2</SAOID>

  <SAODate>2014-05-05T19:25:25.583</SAODate>

  <HeaderAddress>

  <City>cef</City>

  <State>ghi</State>

  </HeaderAddress>

  <Detail>

  <SAODetID>1</SAODetID>

  <productID>1</productID>

  <DetailAddresses>

  <BillAddr>hjj</BillAddr>

  <shipAddr>fre</shipAddr>

  </DetailAddresses>

  </Detail>

  </SalesHeader>

  <SalesHeader>

  <SAOID>2</SAOID>

  <SAODate>2014-05-05T19:25:25.583</SAODate>

  <HeaderAddress>

  <City>cef</City>

  <State>ghi</State>

  </HeaderAddress>

  <Detail>

  <SAODetID>2</SAODetID>

  <productID>2</productID>

  <DetailAddresses>

  <BillAddr>hyi</BillAddr>

  <shipAddr>der</shipAddr>

  </DetailAddresses>

  </Detail>

  </SalesHeader>

  </Sales>

  I would be Very thankful to all for immediate response. Thanks in Advance

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  May 5, 2014 at 10:22 am

  #1710786

  You need to nest your queries to get nested xml. e.g. this gets your details nested (but not the addresses):

  select H.SAOID

  ,H.SAODate

  ,H.City "HeaderAddress/City" --Header Address New Tag

  ,H.State "HeaderAddress/State" --Header Address New Tag

  , (select

  SAODetID "SAODetID" --Detail Info New Tag

  ,productID "productID" --Detail Info New Tag

  ,BillAddr "BillAddr" --Detail Address New TAg

  ,shipAddr "ShipAddr" --Detail Address New TAg

  from @SaoHeaderDetail Detail

  where SAOID = h.SAOID

  for xml AUTO, TYPE, ELEMENTS)

  from @SaoHeader H

  --inner join @SaoHeaderDetail det on H.SAOID=det.SAOID

  for XML PAth('SalesHeader'),Elements,Root('Sales')

  return

  [/code

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  May 5, 2014 at 12:42 pm

  #1710847

  Thanks for your help, your idea really work for me, I further change the query and get my required result.

  Declare @SaoHeader AS Table (SAOID int,SAODate DateTime,City varchar(50),State varchar(50))

  insert into @SaoHeader(SAOID ,SAODate ,City ,State )Values(1,getdate(),'abc','xyz')

  insert into @SaoHeader(SAOID ,SAODate ,City ,State )Values(2,getdate(),'cef','ghi')

  Declare @SaoHeaderDetail AS Table (SAOID int,SAODetID int,productID int,shipAddr varchar(50),BillAddr varchar(50))

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(1,1,1,'abc','xyz')

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(1,2,2,'lep','mno')

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(2,1,1,'fre','hjj')

  insert into @SaoHeaderDetail(SAOID ,SAODetID ,productID ,shipAddr ,BillAddr)Values(2,2,2,'der','hyi')

  select H.SAOID

  ,H.SAODate

  ,H.City "HeaderAddress/City" --Header Address New Tag

  ,H.State "HeaderAddress/State" --Header Address New Tag

  ,

  (

  select

  SAODetID "SAODetID" --Detail Info New Tag

  ,productID "productID" --Detail Info New Tag

  ,(

  select BillAddr "BillAddr" --Detail Address New TAg

  ,shipAddr "ShipAddr" --Detail Address New TAg

  from @SaoHeaderDetail DetailAddresses

  where Detail.SAOID=DetailAddresses.SAOID

  AND Detail.SAODetID=DetailAddresses.SAODetID

  for xml AUTO, TYPE, ELEMENTS

  )

  from @SaoHeaderDetail Detail

  where SAOID = h.SAOID

  for xml AUTO, TYPE, ELEMENTS

  )

  from @SaoHeader H

  --inner join @SaoHeaderDetail det on H.SAOID=det.SAOID

  for XML PAth('SalesHeader'),Elements,Root('Sales')