如何将 SQL 转换为 XML?

Question

>我正在尝试将SQL输出为XML以匹配以下确切格式

<?xml version="1.0" encoding="utf-8"?>
<ProrateImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schema.aldi-
sued.com/Logistics/Shipping/ProrateImport/20151009">
<Prorates>
<Prorate>
<OrderTypeId>1</OrderTypeId>
<DeliveryDate>2015-10-12T00:00:00+02:00</DeliveryDate>
<DivNo>632</DivNo>
<ProrateUnit>1</ProrateUnit>
<ProrateProducts>
<ProrateProduct ProductCode="8467">
<ProrateItems>
<ProrateItem StoreNo="1">
<Quantity>5</Quantity>
</ProrateItem>
<ProrateItem StoreNo="2">
<Quantity>5</Quantity>
</ProrateItem>
<ProrateItem StoreNo="3">
<Quantity>5</Quantity>
</ProrateItem>
</ProrateItems>
</ProrateProduct>
</ProrateProducts>
</Prorate>
</Prorates>
</ProrateImport>

这是我的查询：

SELECT 
OrderTypeID,
DeliveryDate, DivNo,
ProrateUnit,
(SELECT  
ProductOrder [@ProductCode],
(SELECT 
ProrateItem [@StoreNo],
CAST(Quantity AS INT) [Quantity]
FROM 
##Result2 T3
WHERE
T3.DivNo = T2.DivNo 
AND T3.DivNo = T1.DivNo 
AND T3.DeliveryDate = T2.DeliveryDate 
AND T3.DeliveryDate = T1.DeliveryDate
AND T3.ProductOrder = t2.ProductOrder
FOR XML PATH('ProrateItem'), TYPE, ROOT('ProrateItems')
)
FROM 
##Result2 T2
WHERE
T2.DivNo = T1.DivNo 
AND T2.DeliveryDate = T1.DeliveryDate
FOR XML PATH('ProrateProduct'), TYPE, ROOT('ProrateProducts')
)
FROM 
##Result2 T1
GROUP BY 
OrderTypeID, DeliveryDate, DivNo, ProrateUnit
FOR XML PATH('Prorate'), TYPE, ROOT('Prorates')

如何添加以下内容并将按比例导入/20151009"更改为当前日期？

<?xml version="1.0" encoding="utf-8"?>
<ProrateImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schema.aldi-
sued.com/Logistics/Shipping/ProrateImport/20151009">

这是我第一次使用XML。

Answer 1

我不确定我是否理解。您是否自己创建了第一个 XML，只需要添加最后一个脚本？

DECLARE @XMLHEADER nvarchar(max)
SET @XMLHEADER = '<?xml version="1.0" encoding="utf-8"?>
<ProrateImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schema.aldi-sued.com/Logistics/Shipping/ProrateImport/'+convert(varchar(8),getdate(),112)+'"
>'
select @xmlheader

然后，您只需从 select 语句中添加其余输出即可。

Answer 2

有几个问题：

• 如何引入命名空间？
• 如何动态引入命名空间
• 如何添加<?xml ?>指令
• 两级根 (<ProrateImport><Prorate>)

命名空间

您必须使用WITH XMLNAMESSPACES将命名空间引入查询。

提示：裸xmlns由DEFAULT引入，xsi命名空间将使用ELEMENTS XSINIL自动引入：

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' AS xsd
,DEFAULT 'http://schema.aldi-sued.com/Logistics/Shipping/ProrateImport/20151009')
SELECT 1 AS Dummy 
FOR XML PATH('rowElement'), ELEMENTS XSINIL, ROOT('root')

结果

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://schema.aldi-sued.com/Logistics/Shipping/ProrateImport/20151009" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<rowElement>
<Dummy>1</Dummy>
</rowElement>
</root>

注意：命名空间必须按字面说明。没有计算，没有变量！

动态命名空间

这是 - 开箱即用 - 不可能。但是，您可以使用动态创建的 SQL 并使用EXEC来获取结果。只需完全按照上述方式创建语句即可

DECLARE @cmd VARCHAR(MAX)=
'
WITH XMLNAMESPACES(''http://www.w3.org/2001/XMLSchema'' AS xsd
,DEFAULT ''http://schema.aldi-sued.com/Logistics/Shipping/ProrateImport/' + CONVERT(VARCHAR(8),GETDATE(),112)  + ''')
SELECT 1 AS Dummy 
FOR XML PATH(''rowElement''), ELEMENTS XSINIL, ROOT(''root'')';
PRINT @cmd
EXEC(@cmd);

结果

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://schema.aldi-sued.com/Logistics/Shipping/ProrateImport/20171019" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<rowElement>
<Dummy>1</Dummy>
</rowElement>
</root>

命令

该指令不能引入到 XML 中。SQL-Server 将省略任何<?xml ?>指令！这只能在字符串级别完成：

DECLARE @cmd VARCHAR(MAX)=
'
WITH XMLNAMESPACES(''http://www.w3.org/2001/XMLSchema'' AS xsd
,DEFAULT ''http://schema.aldi-sued.com/Logistics/Shipping/ProrateImport/' + CONVERT(VARCHAR(8),GETDATE(),112)  + ''')
SELECT(
SELECT 1 AS Dummy 
FOR XML PATH(''rowElement''), ELEMENTS XSINIL, ROOT(''root'')) AS MyResult';
CREATE TABLE #resultTable(MyXmlAsString VARCHAR(MAX))
INSERT INTO #resultTable(MyXmlAsString)
EXEC(@cmd);
SELECT '<?xml version="1.0" encoding="utf-8"?>' + MyXmlAsString
FROM #resultTable;

结果

<?xml version="1.0" encoding="utf-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://schema.aldi-sued.com/Logistics/Shipping/ProrateImport/20171019" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<rowElement>
<Dummy>1</Dummy>
</rowElement>
</root>

两级根

您可以嵌套两个FOR XML语句来实现此目的：

WITH XMLNAMESPACES(DEFAULT 'blah')
SELECT
(
SELECT 1 AS Dummy
FOR XML PATH('rowElement'),ROOT('innerRoot'),TYPE
)
FOR XML PATH('outerRoot');

但令人讨厌的部分是，命名空间是由每个子选择一遍又一遍地引入的。没有错，但很烦人！一个众所周知的Microsoft连接问题。请登录并投票支持它！结果：

<outerRoot xmlns="blah">
<innerRoot xmlns="blah">   <!--Here's the second xmlns! -->
<rowElement>
<Dummy>1</Dummy>
</rowElement>
</innerRoot>
</outerRoot>

您的解决方案

在解释了所有这些之后，我建议创建没有任何命名空间或声明的XML(您已经在做什么！)，然后将结果转换为NVARCHAR(MAX)并在字符串级别添加页眉和结束页脚。这很丑陋，但在你的情况下是唯一的方法。

提示： 如果不丢失指令，您将无法将最终结果存储在 SQL Server 中的本机 XML 类型中。