how to create xml from xml fragments

Question

I am using SQL Server 2005 and I have a XML Data Type column that stores xml fragments like so in one big table:

row 1 ..... <Order date='2009-02-11' customerID='4' />
row 2...... <OrderItem OrderID='6' ItemID='477' quantity='1' />

I would like to create an XML using T-SQL that looks like this from these nodes:

<Orders>
  <Order data='2009-02-11' customerID='4'>
    <OrderItems>
     <OrderItem OrderID='5' ItemID='477' quantity='1'/>
    </OrderItems>
  </Order>
</Orders>

Any suggestions? Thank-you.

Answer 1

Create a UDF that will call XmlDocument.LoadXml(). The DocumentElement (root) is just any other XmlNodeList.

Answer 2

The biggest hurdle with a pure T-SQL solution is the string concatenation. This little T-SQL solution that I whipped up should do the trick and run fast. It would probably be good to encapsulate this into a UDF though.

CREATE TABLE #Order ( orderId INT PRIMARY KEY,[xmlData] NVARCHAR(512) )
GO 
CREATE TABLE #OrderLines
( orderId INT, orderLine INT,[xmlData] NVARCHAR(512),CONSTRAINT [pk_OrderLines] PRIMARY KEY CLUSTERED (orderId, orderLine) )
GO
INSERT INTO #Order
SELECT 1, '<Order date="2009-02-11" customerID="4">'

INSERT INTO #OrderLines
SELECT 1, 1, '<OrderItem OrderID="1" ItemID="477" quantity="1" />' UNION ALL 
SELECT 1, 2, '<OrderItem OrderID="1" ItemID="478" quantity="1" />'

--
-- Pivot Order lines into one string value
-- 
DECLARE @OrderLines NVARCHAR(MAX)  SET @OrderLines = ''
SELECT @OrderLines = COALESCE(@OrderLines, ' ','') + [xmlData] from #OrderLines 
WHERE orderId = 1


--
-- Join document fragments together into variable.
--
DECLARE @XMLDOC NVARCHAR(MAX)  SET @XMLDOC = ''
SELECT @XMLDOC = COALESCE(@XMLDOC, ' ','') + a.C1 
FROM
(
    SELECT '<Orders>' AS C1 UNION ALL
    SELECT [xmlData] FROM #Order WHERE orderId = 1 UNION ALL
    SELECT '<OrderItems>' UNION ALL
    SELECT @OrderLines UNION ALL
    SELECT '</OrderItems>' UNION ALL
    SELECT '</Order>' UNION ALL
    SELECT '</Orders>'
) a

SELECT @XMLDOC -- OUTPUT RESULT

Answer 3

This is a more straight-forward method using XML DML in SQL Server 2005/2008 although there is a little kludginess to it. Since you can't insert sql:variable directly into XML using the .modify(insert) method of the XML data type, the trick is you have to cast the XML fragments as character strings, concatenate them, then recast them back as XML, move the second fragment inside the first one and delete the remnants of the second. The implementation is not nearly as bad as it sounds:

DECLARE @xmlfrag1 XML
DECLARE @xmlfrag2 XML
DECLARE @xmlfrag3 XML

SET @xmlfrag1 = '<Orders />'
SET @xmlfrag2 = '<Order date="2009-02-11" customerID="4" />'
SET @xmlfrag3 = '<OrderItem OrderID="5" ItemID="477" quantity="1"/>'

SET @xmlfrag1 = CONVERT(XML, (CONVERT(NVARCHAR(MAX), @xmlfrag1) + CONVERT(NVARCHAR(MAX), @xmlfrag2)))
SET @xmlfrag1.modify('insert /*[2] as first into /*[1]')
SET @xmlfrag1.modify('delete /*[2]')
SET @xmlfrag1.modify('insert <OrderItems /> as first into (/Orders/Order)[1]')
SET @xmlfrag1 = CONVERT(XML, (CONVERT(NVARCHAR(MAX), @xmlfrag1) + CONVERT(NVARCHAR(MAX), @xmlfrag3)))
SET @xmlfrag1.modify('insert /*[2] as first into (/Orders/Order/OrderItems)[1]')
SET @xmlfrag1.modify('delete /*[2]')

SELECT @xmlfrag1

This will return the following, which is exactly what you wanted:

<Orders>
  <Order date="2009-02-11" customerID="4">
    <OrderItems>
      <OrderItem OrderID="5" ItemID="477" quantity="1" />
    </OrderItems>
  </Order>
</Orders>

How you get your XML fragments is completely up to you, but this should be enough to get you started.

Answer 4

-- The temporal table #t is your table. The field OrderId is necesary, I assume exists in your table.

create table #t (OrderId int, f xml)
insert #t values (6,'')
insert #t values (6,'')


select 
 1      as tag,
 null     as parent,
 t.OrderId          as [Order!1!!hide],
 f.value('(/Order/@date)[1]','varchar(10)')  as [Order!1!data],
 f.value('(/Order/@customerID)[1]','int')  as [Order!1!customerID],
 null           as [OrderItems!2!!hide],
 null           as [OrderItem!3!OrderID],
 null           as [OrderItem!3!ItemID],
 null           as [OrderItem!3!quantity]
from #t as [t]
where 
 f.value('(/Order/@date)[1]','varchar(10)') is not null -- if is possible change the condition using another field

union all

select distinct
 2      as tag,
 1      as parent,
 t.OrderId          as [Order!1!!hide],
 null           as [Order!1!data],
 null           as [Order!1!customerID],
 1            as [OrderItems!2!!hide],
 null           as [OrderItem!3!OrderID],
 null           as [OrderItem!3!ItemID],
 null           as [OrderItem!3!quantity]
from #t as [t]

union all

select
 3      as tag,
 2      as parent,
 t.OrderId          as [Order!1!!hide],
 null           as [Order!1!data],
 null           as [Order!1!customerID],
 1            as [OrderItems!2!!hide],
 f.value('(/OrderItem/@OrderID)[1]','int')  as [OrderItem!3!OrderID],
 f.value('(/OrderItem/@ItemID)[1]','int')  as [OrderItem!3!ItemID],
 f.value('(/OrderItem/@quantity)[1]','int')  as [OrderItem!3!quantity]
from #t as [t]
where 
 f.value('(/OrderItem/@OrderID)[1]','int') is not null-- if is possible change the condition using another field


ORDER BY 
 [Order!1!!hide],
 [OrderItems!2!!hide],
 [OrderItem!3!OrderID]

FOR XML EXPLICIT, ROOT('Orders'), TYPE