TSQL For Xml Path query to get nested data
-
I have one xml file with nested data when the xml is loaded with SSIS; it produces 3 tables. I created tables in SQL Server.
The 3 tables are:
1.Customer (95 columns)
2.Booking - (46 columns)
3.Customer_Booking_ID - (2 columns) is empty in the xml before loading the data but it populated after loading the data which provide the ID’s to link the table (not sure how it done to recreate in my own xml)
I now have to recreate the same xml format but with more data but I’m currently having problems producing a nested XML format like below:
<?xml version="1.0"?><Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Customers.xsd">
<Customer>
<AddressID>12132434454349</AddressID>
<AddressOptin>1</AddressOptin>
<CustomerID>12132434454349</CustomerID>
<Customer_Booking_ID>
<Booking>
<BookingTypeCode>LastBooked</BookingTypeCode>
<Complained>0</Complained>
</Booking>
<Booking>
<BookingTypeCode>LastReturned</BookingTypeCode>
<BalanceOutstanding>2333.12</BalanceOutstanding>
<BookingReference>ref1232343454</BookingReference>
<EngagementChannelCode>CC</EngagementChannelCode>
<EnquiryReference>ref1232343454</EnquiryReference>
<Complained>0</Complained>
</Booking>
</Customer_Booking_ID>
<LastBrochureRequestDate>2013-07-24</LastBrochureRequestDate>
<LastComplaintIssuedDate>2013-07-26</LastComplaintIssuedDate>
</CustomerContact>
</Customers>
Create Table dbo.Customers( AddressID Varchar(50)
,AddressOptin int
,CustomerID int
,LastBrochureRequestDate INT
,LastComplaintIssuedDate INT
,Complained Varchar(50))
Create Table dbo.Customer_Booking_ID
(customerID int /** not in the xml file create during loading data **/
,bookingID int) /** not in the xml file create during loading data **/
Create Table Booking (
BookingTypeCode Varchar(50)
,BalanceOutstanding int
,BookingReference Varchar(50)
,EngagementChannelCode Varchar(50)
,EnquiryReference Varchar(50)
,BookingID int) ****
*** bookingID is also in the Booking Table but is not in the xml file but it created during ssis loading into the tables.
INSERT into Customer_Booking_ID ([customerID],[bookingID])
Values(1, 1),
(2, 2)
INSERT INTO Customers (AddressID, AddressOptin,CustomerID,LastBrochureRequestDate,LastComplaintIssuedDate,Complained)
VALUES ('1111', 1,1111,'2013-12-12','2013-12-12',0),
('2222', 1,2222,'2013-12-12','2013-12-12',0)
INSERT INTO dbo.Booking (BookingTypeCode,BalanceOutstanding,BookingReference,EngagementChannelCode,EnquiryReference,BookingID)
VALUES ('LastReturned', 222.13,'BookingREF11','CC','EEREF11',1),
('LastReturned', 222.13,'BookingREF12','CC','EEREF12',2)
-
Is the description of the problem not clear or the problem not that easy to solve?
Any help appreciated,
Thanks.
-
SELECT c.AddressID,
c.AddressOptin,
c.CustomerID,
(
SELECT b.BookingTypeCode,
b.BalanceOutstanding,
b.BookingReference,
b.EngagementChannelCode,
b.EnquiryReference
FROM Booking b
INNER JOIN Customer_Booking_ID cb ON b.BookingID = cb.BookingID
WHERE cb.CustomerID = c.CustomerID
FOR XML PATH('Booking'),ROOT('Customer_Booking_ID'),TYPE
),
c.LastBrochureRequestDate,
c.LastComplaintIssuedDate
FROM Customers c
FOR XML PATH('Customer'),ROOT('Customers'),TYPE;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you, it just what I need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply