代码之家 › 专栏 › 技术社区 › JohnLBevan

如何将xmlns:*属性与SQL匹配?

sql-server-2017 xml-namespaces xml sql-server

JohnLBevan · 技术社区 · 6 年前

问题

在SQLServer的命名空间轴上使用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

研究

NB:有一个 similar question 询问这是如何在XSLT中完成的;但是SQL Server不包括 namespace:: 轴。SQL中可用轴的列表可用 here .

有其他方法可以消除这个膨胀;但是没有一种方法是直接的,而且这些帖子现在已经很过时了,因此我的研究方法是:

对各种可用方法的良好概述: 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=required
在SQL中生成XML时如何避免这个问题的几个答案 How do I remove redundant namespace in nested query when using 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

1 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

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更复杂,嵌套,不管怎样,这将变得非常糟糕。。。