none
FOR XML PATH

    Question

  • Hi all,

    I have a query that returns an xml document. I have used xml path and sub queries to return nested nodes, including OrderHeader > OrderStores > OrderLines.

    It is all working as expected, however, I need to create a 'document header node' that closes before the OrderHeader node.

    Here is some test code:

    use tempdb;
    
    if not exists (select * from sys.objects where name = N'Orders' and type = N'U')
     
    create table Orders (
    	id int identity(1,1) primary key,
    	orderNumber varchar(30) not null
    );
    
    go
    
    if not exists (select * from sys.objects where name = N'OrderDetails' and type = N'U')
    
    create table OrderDetails (
    	id int identity(1,1) primary key,
    	OrderId int foreign key references Orders(id),
    	ProductCode varchar(30)) ;
    
    
    declare @OrdId int;
    
    insert into Orders
    values ('Ord1') ;
    
    set @OrdId = SCOPE_IDENTITY();
    
    
    insert into OrderDetails
    select @OrdId, 'Product1'
    union all
    select @OrdId, 'Product2'
    union all
    select @OrdId, 'Product3';
    
    
    
    insert into Orders
    values ('Ord2') ;
    
    set @OrdId = SCOPE_IDENTITY();
    
    
    insert into OrderDetails
    select @OrdId, 'Product1'
    union all
    select @OrdId, 'Product2'
    union all
    select @OrdId, 'Product3';
    
    
    
    
    select 
    orderNumber as "Order",
    (select 
    ProductCode
    from OrderDetails
    where Orders.id = OrderDetails.OrderId
    for xml path(''), type)  as "OrderDetail"
    from 
    Orders 
    for xml path ('OrderHeader'), elements
    
    
    delete from OrderDetails;
    delete from Orders;

    Here is what the XML currently looks:

    <OrderHeader>
      <Order>Ord1</Order>
      <OrderDetail>
        <ProductCode>Product1</ProductCode>
        <ProductCode>Product2</ProductCode>
        <ProductCode>Product3</ProductCode>
      </OrderDetail>
    </OrderHeader>
    <OrderHeader>
      <Order>Ord2</Order>
      <OrderDetail>
        <ProductCode>Product1</ProductCode>
        <ProductCode>Product2</ProductCode>
        <ProductCode>Product3</ProductCode>
      </OrderDetail>
    </OrderHeader>

    Here is how I want it to look:

    <DocumentHeader>
    	<DocumentType>Order</DocumentType>
    	<HostApplication>SAP</HostApplication>
    	<DateCreated>12/12/2012</DateCreated>
    </DocumentHeader>
    <OrderHeader>
      <Order>Ord1</Order>
      <OrderDetail>
        <ProductCode>Product1</ProductCode>
        <ProductCode>Product2</ProductCode>
        <ProductCode>Product3</ProductCode>
      </OrderDetail>
    </OrderHeader>
    <OrderHeader>
      <Order>Ord2</Order>
      <OrderDetail>
        <ProductCode>Product1</ProductCode>
        <ProductCode>Product2</ProductCode>
        <ProductCode>Product3</ProductCode>
      </OrderDetail>
    </OrderHeader>

    My issue is the fact that the DocumentHeader element closes i.e. I cannot simply nest the order header query within a 'document header' query.

    Thanks for any assistance.

    Clay

    Tuesday, January 29, 2013 2:54 AM

Answers

  • Hi Clay,

               Using XML DML method - modify() add <DocumentHeader> node before <OrderHeader> node.


    Thanks & Regards, sathya

    • Marked as answer by clay123123123 Tuesday, January 29, 2013 4:35 AM
    Tuesday, January 29, 2013 3:09 AM
    Moderator
  • Adding to Saythya's response, you can set the result to an XML variable and then invoke modify:

    DECLARE @xml xml = (
    	SELECT 
    		orderNumber as "Order",
    		(SELECT 
    			ProductCode
    		FROM dbo.OrderDetails
    		where Orders.id = OrderDetails.OrderId
    		FOR XML PATH(''), TYPE)  AS "OrderDetail"
    	FROM dbo.Orders 
    	FOR XML PATH ('OrderHeader'), ELEMENTS);
    
    DECLARE @DocumentHeader xml = '<DocumentHeader>
    	<DocumentType>Order</DocumentType>
    	<HostApplication>SAP</HostApplication>
    	<DateCreated>12/12/2012</DateCreated>
    </DocumentHeader>'
    
    SET @xml.modify('
    insert sql:variable("@DocumentHeader")
    before (/OrderHeader[1])');
    
    SELECT @xml;
    
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by clay123123123 Tuesday, January 29, 2013 4:35 AM
    Tuesday, January 29, 2013 3:56 AM

All replies

  • Hi Clay,

               Using XML DML method - modify() add <DocumentHeader> node before <OrderHeader> node.


    Thanks & Regards, sathya

    • Marked as answer by clay123123123 Tuesday, January 29, 2013 4:35 AM
    Tuesday, January 29, 2013 3:09 AM
    Moderator
  • Adding to Saythya's response, you can set the result to an XML variable and then invoke modify:

    DECLARE @xml xml = (
    	SELECT 
    		orderNumber as "Order",
    		(SELECT 
    			ProductCode
    		FROM dbo.OrderDetails
    		where Orders.id = OrderDetails.OrderId
    		FOR XML PATH(''), TYPE)  AS "OrderDetail"
    	FROM dbo.Orders 
    	FOR XML PATH ('OrderHeader'), ELEMENTS);
    
    DECLARE @DocumentHeader xml = '<DocumentHeader>
    	<DocumentType>Order</DocumentType>
    	<HostApplication>SAP</HostApplication>
    	<DateCreated>12/12/2012</DateCreated>
    </DocumentHeader>'
    
    SET @xml.modify('
    insert sql:variable("@DocumentHeader")
    before (/OrderHeader[1])');
    
    SELECT @xml;
    
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by clay123123123 Tuesday, January 29, 2013 4:35 AM
    Tuesday, January 29, 2013 3:56 AM
  • Just nest that data as a subquery, no need to use additional DML modify method which would be expensive, eg

    select
    	(
    	select 
    		'Order' AS DocumentType,
    		'SAP' AS HostApplication,
    		'12/12/2012' AS DateCreated
    	for xml path('DocumentHeader'), type
    	),
    	(
    	select
    		orderNumber as "Order",
    		(
    		select 
    		ProductCode
    		from OrderDetails
    		where Orders.id = OrderDetails.OrderId
    		for xml path(''), type
    		)  as "OrderDetail"
    	from Orders 
    	for xml path ('OrderHeader'), type
    	)
    for xml path(''), type

    Tuesday, January 29, 2013 7:30 AM