December 15, 2008 at 2:47 am
Hi all,
I need to produce some XML from a query, however I'm a little confused.
Consider the following, I have a customer table, this joins to an order table where a customer can have many orders. When I right my query I get the data from the customer table and join to the order, in this scenario it returns a record for each order the customer(s) have made. Now I want to export this to XML but I want it to be structured like this (see attachment)
When I right it using a PATH, the best I can get it to do is one XML block for each customer with the one order in it, when I want it to be one customer node (for each customer) with all the orders in it (as above). I attached a sample select to demonstrate how the query will produce the data.
Any help would be appreciated.
Thanks,
Jackal
December 15, 2008 at 3:04 am
When you use path mode and you want to have hirarchies you need to use correlated queries. The script bellow creates 2 tables, insert some data into them and shows you how to get the XML using auto mode and path mode.
if exists (select * from sys.objects where name = 'Customers')
drop table Customers
go
--Creating customers table
create table Customers (CustomerID varchar(15) not null primary key,
ContactName varchar(20),
CompanyName varchar(40))
go
--First part creating the demo tables and populate them
-------------------------------------------------------------
IF EXISTS (select * from sys.objects where name = 'Orders')
drop table Orders
go
--Creating Orders table
create table Orders (OrderID int not null primary key,
CustomerID varchar(15) not null,
OrderDate datetime not null)
go
insert into Customers (CustomerID, ContactName, CompanyName)
select 'ALFKI','Maria Anders','Alfreds Futterkiste'
union select 'ANATR','Ana Trujillo','Ana Trujillo Emparedados y helados'
go
Insert into Orders (OrderID, CustomerID, OrderDate)
select 10643,'ALFKI','Aug 25 1997 12:00AM'
union select 10692,'ALFKI','Oct 3 1997 12:00AM'
union select 10702,'ALFKI','Oct 13 1997 12:00AM'
union select 10308,'ANATR','Sep 18 1996 12:00AM'
union select 10625,'ANATR','Aug 8 1997 12:00AM'
go
--Using for xml and Auto mode
SELECT Customers.CustomerID, ContactName, CompanyName,
OrderDate
FROM Customers inner join orders on Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML AUTO, root('Root')
go
--second way - using path mode. Notice the correlated sub query in the select
--clause and that I have to use the directive type.
SELECTCustomers.CustomerID,
ContactName, CompanyName,
(select OrderDate
From Orders
Where CustomerID = Customers.CustomerID
FOR XML PATH(''), type) as 'OrdersList'
FROM Customers
ORDER BY Customers.CustomerID
FOR XML path('') , root('Root')
go
drop table Orders
go
drop table Customers
go
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 15, 2008 at 3:35 am
Hi thats great, I was on the right tracks to figuring it out and your examples filled in the gaps, so many thanks.
However, lets say that for each order date node we have returned we wish to include additional data, would this be possible? I've attached an sample of what I mean.
Thanks,
Jackal
December 15, 2008 at 3:57 am
You can do it. You have to include the price in your query. I don't have your tables' structure so I can't show you how to do it, but if you'll add the price column to the correlated sub query, it will show up for each order.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 15, 2008 at 4:04 am
Yes it does return it when I add it into the query, but the issue is that I need to close the tag.
December 15, 2008 at 4:09 am
Hi, scrub that comment.
Think I have it now, had to move a few bits and bobs round but it seems to be on the right lines now.
Thanks again for you help,
Jackal.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply