如何从xml片段创建xml

这是一种在SQLServer2005/2008中使用XMLDML的更直接的方法,尽管它有一点模糊。因为你不能插入sql:直接变量使用XML数据类型的.modify(insert)方法将XML片段转换为字符串,将它们连接起来,然后将它们重新转换为XML,将第二个片段移到第一个片段的内部并删除第二个片段的剩余部分。实施情况并不像听起来那么糟糕:

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

这将返回以下内容,这正是您想要的:

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

如何获得XML片段完全取决于您,但这应该足以让您开始。

创建一个将调用XmlDocument.LoadXml(). DocumentElement(根)只是任何其他XmlNodeList。

纯T-SQL解决方案的最大障碍是字符串连接。我提出的这个小小的T-SQL解决方案应该能做到这一点,而且运行速度很快。不过,最好将其封装到一个UDF中。

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

--时间表是你的表。字段OrderId是必需的,我假设您的表中存在。

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