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

Generate XML From SQL Query Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 8:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:49 AM
Points: 138, Visits: 448
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
Post #1567533
Posted Monday, May 5, 2014 10:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 326, Visits: 730
You need to nest your queries to get nested xml. e.g. this gets your details nested (but not the addresses):

[code]
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
Post #1567607
Posted Monday, May 5, 2014 12:42 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:49 AM
Points: 138, Visits: 448
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')

Post #1567679
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse