问题
有什么方法可以使用SQL Server中与命名空间轴匹配的XPath吗?也就是说,我知道SQL本身并不支持这个轴;但是,是否有任何功能相似的查询可以工作?
上下文
我希望编写代码来消除XML中重复的名称空间,只留下根元素上存在的声明。我见过其他各种解决方案,但都很痛苦;所以我研究了替代方案&在这样做的过程中,意识到SQL不支持名称空间轴。
declare @demo xml = '
<hello:a xmlns:hello="test" xmlns:world="me">
<hello:b>
<world:c xmlns:world="me">demo</world:c>
<hello:d xmlns:hello="test">demo</hello:d>
<world:e xmlns:hello="test" xmlns:world="me">demo</world:e>
<hello:f xmlns:hello="test" xmlns:world="me" world:demo=''x''>demo</hello:f>
</hello:b>
</hello:a>
'
set @demo.modify('delete (/*//namespace::*)')
--set @demo.modify('delete (/*//@*[not(namespace-uri() > "")])') --tried just in case xmlns is treated as an attribute in SQL; no joy :/
select @demo
研究
注意:还有一个类似的问题问XSLT是如何做到这一点的;但是SQL Server不包括CCD_ 1轴。这里提供了SQL中可用轴的列表。
还有其他方法可以消除这种膨胀;但没有一篇是直截了当的,而且这些帖子现在已经过时了,因此我研究了另一种方法:
- 各种可用方法的良好概述:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2f7bdfbf-8e40-456b-84e8-195318649703/how-to-remove-namespaces-from-xml-tags-when-using-for-xml-option-with-xmlnamespaces?forum=transactsql&prof=必需
- 关于在SQL中生成XML时如何避免此问题的几个答案使用FOR XML PATH时如何删除嵌套查询中的冗余命名空间
当前解决方案
注意:由于我一直无法找到一个直接的解决方案,我目前删除这些名称空间的方法如下。这样做会有一些风险(例如,丢失根元素上的属性,如果缺少预期的空格/使用其他空白字符,则会出现问题(,但这对我来说已经足够好了,如果非常棘手的话;非通用。
declare @demo xml = '
<hello:a xmlns:hello="test" xmlns:world="me">
<hello:b>
<world:c xmlns:world="me">demo</world:c>
<hello:d xmlns:hello="test">demo</hello:d>
<world:e xmlns:hello="test" xmlns:world="me">demo</world:e>
<hello:f xmlns:hello="test" xmlns:world="me" world:demo=''x''>demo</hello:f>
</hello:b>
</hello:a>
'
;with xmlnamespaces('test' as hello, 'me' as world)
select @demo = cast(
'<hello:a xmlns:hello="test" xmlns:world="me">'
+ replace(
replace(
cast(@demo.query('/*/*') as nvarchar(max))
,' xmlns:hello="test"'
,''
)
,' xmlns:world="me"'
,''
)
+ '</hello:a>'
as xml
)
select @demo
SQL Server处理XML名称空间的能力真的很痛苦。。。
我知道的定义名称空间的唯一方法是FOR XML EXPLICIT
(除非你想走字符串操作路线…(
您可以使用创建所需的XML
SELECT 1 AS Tag
,NULL AS Parent
,'test' AS [hello:a!1!xmlns:hello]
,'me' AS [hello:a!1!xmlns:world]
,NULL AS [hello:b!2]
,NULL AS [world:c!3]
,NULL AS [hello:d!4]
,NULL AS [world:e!5]
,NULL AS [hello:f!6]
,NULL AS [hello:f!6!world:demo]
UNION ALL
SELECT 2
,1
,NULL
,NULL
,''
,NULL
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT 3
,2
,NULL
,NULL
,''
,'demo'
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT 4
,2
,NULL
,NULL
,''
,NULL
,'demo'
,NULL
,NULL
,NULL
UNION ALL
SELECT 5
,2
,NULL
,NULL
,''
,NULL
,NULL
,'demo'
,NULL
,NULL
UNION ALL
SELECT 6
,2
,NULL
,NULL
,''
,NULL
,NULL
,NULL
,'demo'
,'x'
FOR XML EXPLICIT;
结果
<hello:a xmlns:hello="test" xmlns:world="me">
<hello:b>
<world:c>demo</world:c>
<hello:d>demo</hello:d>
<world:e>demo</world:e>
<hello:f world:demo="x">demo</hello:f>
</hello:b>
</hello:a>
正如Jeroen Mostert在评论中指出的那样,你可能会使用过时的FROM OPEN XML
,比如这里:
declare @demo xml =
'<hello:a xmlns:hello="test" xmlns:world="me">
<hello:b>
<world:c xmlns:world="me">demo</world:c>
<hello:d xmlns:hello="test">demo</hello:d>
<world:e xmlns:hello="test" xmlns:world="me">demo</world:e>
<hello:f xmlns:hello="test" xmlns:world="me" world:demo=''x''>demo</hello:f>
</hello:b>
</hello:a>';
DECLARE @hdoc int;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @demo;
SELECT *
FROM OPENXML(@hdoc, '//*');
EXEC sp_xml_removedocument @hdoc;
GO
结果
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| id | parentid | nodetype | localname | prefix | namespaceuri | datatype | prev | text |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 0 | NULL | 1 | a | hello | test | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 2 | 0 | 2 | hello | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 20 | 2 | 3 | #text | NULL | NULL | NULL | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 3 | 0 | 2 | world | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 21 | 3 | 3 | #text | NULL | NULL | NULL | NULL | me |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 4 | 0 | 1 | b | hello | test | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 5 | 4 | 1 | c | world | me | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 6 | 5 | 2 | world | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 22 | 6 | 3 | #text | NULL | NULL | NULL | NULL | me |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 7 | 5 | 3 | #text | NULL | NULL | NULL | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 8 | 4 | 1 | d | hello | test | NULL | 5 | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 9 | 8 | 2 | hello | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 23 | 9 | 3 | #text | NULL | NULL | NULL | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 10 | 8 | 3 | #text | NULL | NULL | NULL | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 11 | 4 | 1 | e | world | me | NULL | 8 | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 12 | 11 | 2 | hello | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 24 | 12 | 3 | #text | NULL | NULL | NULL | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 13 | 11 | 2 | world | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 25 | 13 | 3 | #text | NULL | NULL | NULL | NULL | me |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 14 | 11 | 3 | #text | NULL | NULL | NULL | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 15 | 4 | 1 | f | hello | test | NULL | 11 | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 16 | 15 | 2 | hello | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 26 | 16 | 3 | #text | NULL | NULL | NULL | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 17 | 15 | 2 | world | xmlns | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 27 | 17 | 3 | #text | NULL | NULL | NULL | NULL | me |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 18 | 15 | 2 | demo | world | me | NULL | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 28 | 18 | 3 | #text | NULL | NULL | NULL | NULL | x |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 19 | 15 | 3 | #text | NULL | NULL | NULL | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
该表包含在递归CTE中动态创建上述语句并使用EXEC
从头开始创建XML所需的所有信息。
使用WHERE nodetype=1
可以获得元素,使用2
可以获得属性。。。
但是,老实说,这是一项巨大的努力。。。
如果XML更复杂、嵌套,不管怎样,这都会变得非常糟糕。。。