XML Query Output

Question

Hi,

I try to create a query that return a XML result....

I have these temp tables:

create table Master
(
    MasterID int,
    BatchDate date,
    VendorName varchar(20),
    Amount money
)

create table Invoices
(
    InvoiceID int,
    MasterID int,
    Amount money,
    AccountNumber varchar(20)
)

create table InvoiceItems
(
    InvoiceItemID int,
    InvoiceID int,
    Amount money,
    ItemName varchar(20),
    MinistryName varchar(20),
    ItemNumber int
)

create table InvoiceItemsDetails
(
    InvoiceItemDetailID int,
    InvoiceItemID int,
    Amount money,
    [Description] varchar(20),
    StartDate date,
    EndDate date,
    OrderNumber int,
    Qty int,
    Rate money,
    Unit varchar(20)
)

insert into Master values (1, GETDATE()-30, 'Vendor Name 1', 30.23)
insert into Invoices values (1, 1, 12.35, '542154664')
insert into InvoiceItems values (1, 1, 30.58, 'New York', 'US', 45)
insert into InvoiceItemsDetails values (1, 1, 58.96, 'Test Service', GETDATE(), GETDATE()+30, 8324792, 25, 2.45, 'UT')

I need to create an XML with this format:

<InvoiceBatch>
    
    <BatchDate></BatchDate>
    
    <VendorName></VendorName>
    
    <Amount></Amount>
    
    <Invoices>
        
        <Amount></Amount>
        
        <AccountNumber></AccountNumber>
        
        <InvoiceItems>
            <Amount></Amount>
            <ItemName></ItemName>
            <MinistryName></MinistryName>
            <ItemNumber></ItemNumber>
            
            <InvoiceItemDetails xsi:type="Service">
                <Amount></Amount>
                <Description></Description>
                <StartDate></StartDate>
                <EndDate></EndDate>
                <OrderNumber></OrderNumber>
                <Qty></Qty>
                <Rate></Rate>
                <Unit></Unit>
            </InvoiceItemDetails>
                       
        </InvoiceItems>
        
    </Invoices>
    
</InvoiceBatch>

I don't have experience using XML Path, any idea?? I have problems with nested nodes...

Thank you.

Answer 1

Try this...

create table [Master]
(
     MasterID int,
     BatchDate date,
     VendorName varchar(20),
     Amount money
 )
 
create table Invoices
 (
     InvoiceID int,
     MasterID int,
     Amount money,
     AccountNumber varchar(20)
 )
 
create table InvoiceItems
 (
     InvoiceItemID int,
     InvoiceID int,
     Amount money,
     ItemName varchar(20),
     MinistryName varchar(20),
     ItemNumber int
 )
 
create table InvoiceItemsDetails
 (
     InvoiceItemDetailID int,
     InvoiceItemID int,
     Amount money,
     [Description] varchar(20),
     StartDate date,
     EndDate date,
     OrderNumber int,
     Qty int,
     Rate money,
     Unit varchar(20)
 )
 

insert into [Master] values (1, GETDATE()-30, 'Vendor Name 1', 30.23)
insert into Invoices values (1, 1, 12.35, '542154664')
insert into InvoiceItems values (1, 1, 30.58, 'New York', 'US', 45)
insert into InvoiceItemsDetails values (1, 1, 58.96, 'Test Service', GETDATE(), GETDATE()+30, 8324792, 25, 2.45, 'UT')

select
	m.BatchDate,
	m.VendorName,
	m.Amount,
	i.Amount as "Invoices/Amount",
	i.AccountNumber as "Invoices/AccountNumber",
	ii.Amount as "Invoices/InvoiceItems/Amount",
	ii.ItemName as "Invoices/InvoiceItems/ItemName",
	ii.MinistryName as "Invoices/InvoiceItems/MinistryName",
	ii.ItemNumber as "Invoices/InvoiceItems/ItemNumber",
	iid.Amount as "Invoices/InvoiceItems/InvoiceItemDetails/Amount",
	iid.[Description]as "Invoices/InvoiceItems/InvoiceItemDetails/Description",
	iid.StartDate as "Invoices/InvoiceItems/InvoiceItemDetails/StartDate",
	iid.EndDate as "Invoices/InvoiceItems/InvoiceItemDetails/EndDate",
	iid.OrderNumber as "Invoices/InvoiceItems/InvoiceItemDetails/OrderNumber",
	iid.Qty as "Invoices/InvoiceItems/InvoiceItemDetails/Qty",
	iid.Rate as "Invoices/InvoiceItems/InvoiceItemDetails/Rate",
	iid.Unit as "Invoices/InvoiceItems/InvoiceItemDetails/Unit"
From
	[Master] m
	Inner Join
		Invoices i
	on
		m.MasterID = i.MasterID
	inner join
		InvoiceItems ii
	on
		i.InvoiceID = ii.InvoiceID
	inner join 
		InvoiceItemsDetails iid
	on
		ii.InvoiceItemID = iid.InvoiceItemID
For XML PATH('InvoiceBatch')

Drop Table [Master];
Drop Table Invoices;
Drop Table InvoiceItems;
Drop Table InvoiceItemsDetails;

Thanks

---

/Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem

Answer 2

This is really good, thanks a lot!

Answer 3

Sorry, I have one more thing to add :

I need to add in the node InvoiceItemDetails, this hardcoded value:

<InvoiceItemDetails xsi:type="Service">

How can I add this?

Thanks

Answer 4

I don't think that other query will work.  It assumes all relationships are one-to-one, where Invoices-to-InvoiceItems and InvoiceItems-to-InvoiceItemsDetails sound like one-to-many.  Is that correct?

Try something like this instead:

DECLARE @xml XML

SET @xml = 
(
SELECT
	m.BatchDate,
	m.VendorName,
	m.Amount,
	(
	SELECT 
		i.Amount,
		i.AccountNumber,
		(
		SELECT 
			ii.Amount,
			ii.ItemName,
			ii.MinistryName,
			ii.ItemNumber,
			(
			SELECT				
				iid.Amount,
				iid.Description,
				iid.StartDate,
				iid.EndDate,
				iid.OrderNumber,
				iid.Qty,
				iid.Rate,
				iid.Unit
			FROM dbo.InvoiceItemsDetails iid
			WHERE ii.InvoiceItemID = iid.InvoiceItemID
			FOR XML PATH('InvoiceItemDetails'), TYPE
			)
		FROM dbo.InvoiceItems ii
		WHERE i.InvoiceID = ii.InvoiceID
		FOR XML PATH('InvoiceItems'), TYPE
		)
	FROM dbo.Invoices i
	WHERE m.MasterID = i.MasterID
	FOR XML PATH('Invoices'), TYPE
	)
FROM dbo.Master m
FOR XML PATH('InvoiceBatch'), TYPE
)

SET @xml.modify('insert attribute xsi:type{"service"} as first into (InvoiceBatch/Invoices/InvoiceItems/InvoiceItemDetails)[1]')

SELECT @xml

Answer 5

Hi,

yes the relation is one to many.

I checked your answer, the string is added, but also is adding this xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance", this I believe is automatic right? Is there any way of removing it?

Thank you. 

Answer 6

You can't remove it.  A namespace must have a declaration. 

Adding Namespaces Using WITH XMLNAMESPACES
http://msdn.microsoft.com/en-us/library/ms177400.aspx