将 xml-namespaces 限制为仅主根
时间：2022-12-31问题描述
我有这个查询
WITH XMLNAMESPACES(DEFAULT 'https://tribunet.hacienda.go.cr /docs/esquemas/2017/v4.2/facturaElectronica'
,'http://www.w3.org/2001/XMLSchema' AS xsd
,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT 1 AS [id]
,0 AS [pass]
(
/*Others*/
SELECT
OT.OTH_MESSAGE as Others
FROM [crdx_COREDev1].[dbo].[OTH_OTHERS] as OT
where
OT.OTH_ID=E.OTH_ID
fOR XML PATH ('Others'), type
)
,0 AS [CONSECUTIVE]
FOR XML PATH('FE');
这会生成这个 XML
<FE xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2 /facturaElectronica"> <- CHANGE 2
<id>1</id>
<pass>0</pass>
<CONSECUTIVE>0</CONSECUTIVE>
<Others xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2 /facturaElectronica">
<MESSAGE>MESSAGE</MESSAGE>
</Others>
</FE>
现在我的问题:我只希望
Now my question: I would like only <FE> to show the namespaces, but - as you see in the xml - that declarations appear also in <Others>. How can I limit this to <FE>?
推荐答案
这是一个令人讨厌且众所周知的问题，每当您在 FOR XML 查询中使用与嵌套子查询相关的命名空间时，就会发生此问题...
This is an annoying and well known issue and occurs whenever you use namespaces in connection with nested sub-queries in FOR XML queries...
连接问题已存在 10 多年 - 直到最近消失.
There has been a connect issue for more than 10 years - until it disappaered recently.
值得一提的是，这些重复的命名空间声明并没有错，只是让您的 XML 变得臃肿.它可能会与(以)严格的模式验证相冲突.
It is important to mention, that these repeated namespace declarations are not wrong, just bloating your XML. And it can collide with (to) strict schema validations.
没有好的解决方案，只有解决方法:
No good solution, just workarounds:
- 创建没有命名空间的内部 XML 并在字符串基础上添加包装节点，或
- 将命名空间创建为普通属性(但未命名为
xmlns)并使用REPLACE更改名称.
- Create the inner XML without the namespace and add the wrapping node on string base, or
- Create the namespaces as normal attributes (but not named
xmlns) and useREPLACEto change the names.
两种解决方法都需要转换为 NVARCHAR(MAX) 并返回到 XML.
Both workarounds need a conversion to NVARCHAR(MAX) and back to XML.
我真的不知道，为什么要以这种方式实现...
I really have no idea, why this was implemented this way...
找一些相关的例子
- 此处
- 和此处
- 和此处
- 和此处
注意:
xmlns="https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2 /facturaElectronica">
您正在使用带空格的命名空间 URL.这是不允许的...
You are using namespace URLs with blanks. This is not allowed...
这篇关于将 xml-namespaces 限制为仅主根的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
相关文章
将包含以科学记数法表示的数字的 varchar 转换为十进制时出错Error casting varchar containing a number expressed in scientific notation to decimal(将包含以科学记数法表示的数字的 varchar 转换为十进制时出错)- 计算累积产品价值Calculate cumulative product value(计算累积产品价值)
- 用于更改不同用户的多个值的 SQL Server 循环SQL Server loop for changing multiple values of different users(用于更改不同用户的多个值的 SQL Server 循环)
- SQL Server 2008:多语句 UDF 能否返回 UDT?SQL Server 2008: Can a multi-statement UDF return a UDT?(SQL Server 2008:多语句 UDF 能否返回 UDT?)
- 合并sql中的列Merge columns in sql(合并sql中的列)
- SQL 列在 ORDER BY 中无效，未包含在聚合或 GROUP BY 中SQL Column is invalid in ORDER BY, not contained in aggregate or GROUP BY(SQL 列在 ORDER BY 中无效，未包含在聚合或 GROUP BY 中)