sql-server - 当 URI 未知时，XPath 是否有定义的方法从 namespace 的缩写检索 URI？

Question

我正在尝试编写一个 XPath 查询来提取始终使用相同缩写的特定命名空间的 URI。

示例:

<RequestPacket xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:a="urn:dont:really:care">
    <a:Requests xmlns:queryNS="urn:uri:i:need">
        <a:Request xsi:type="queryNS:GetImportantData">
            <queryNS:Version>3</queryNS:Version>
            ...
        </a:Request>
    </a:Requests>
</RequestPacket>

我需要提取与 queryNS 命名空间相对应的 URI，在我的示例中为 urn:uri:i:need。

这可能吗？如果是这样，有人可以帮忙吗？

我尝试使用 namespace-uri() 函数来获取它，但要做到这一点，我必须指定一个节点，这只能通过指定每个节点的命名空间来实现(这就是我想弄清楚的)。所以这看起来就像是先有鸡还是先有蛋的情况。

谢谢!

Answer 1

有两种方法:

DECLARE @xml XML=
N'<RequestPacket xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:a="urn:dont:really:care">
    <a:Requests xmlns:queryNS="urn:uri:i:need">
        <a:Request xsi:type="queryNS:GetImportantData">
            <queryNS:Version>3</queryNS:Version>
            ...
        </a:Request>
    </a:Requests>
</RequestPacket>';

--对位于此命名空间内的节点使用 namespace-uri()

SELECT @xml.value(N'namespace-uri((//*:Version)[1])',N'nvarchar(max)')

--这可能是罕见的情况之一，其中 FROM OPENXML is still helpful 。试试这个:

DECLARE @docHandle INT
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml;
SELECT * FROM OPENXML (@docHandle, '',1)   
EXEC sp_xml_removedocument @docHandle  

结果

+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| id | parentid | nodetype | localname     | prefix  | namespaceuri                              | datatype | prev | text                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 0  | NULL     | 1        | RequestPacket | NULL    | NULL                                      | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 2  | 0        | 2        | xsi           | xmlns   | NULL                                      | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 10 | 2        | 3        | #text         | NULL    | NULL                                      | NULL     | NULL | http://www.w3.org/2001/XMLSchema-instance |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 3  | 0        | 2        | a             | xmlns   | NULL                                      | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 11 | 3        | 3        | #text         | NULL    | NULL                                      | NULL     | NULL | urn:dont:really:care                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 4  | 0        | 1        | Requests      | a       | urn:dont:really:care                      | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 5  | 4        | 2        | queryNS       | xmlns   | NULL                                      | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 12 | 5        | 3        | #text         | NULL    | NULL                                      | NULL     | NULL | urn:uri:i:need                            |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 6  | 4        | 1        | Request       | a       | urn:dont:really:care                      | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 7  | 6        | 2        | type          | xsi     | http://www.w3.org/2001/XMLSchema-instance | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 13 | 7        | 3        | #text         | NULL    | NULL                                      | NULL     | NULL | queryNS:GetImportantData                  |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 8  | 6        | 1        | Version       | queryNS | urn:uri:i:need                            | NULL     | NULL | NULL                                      |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 14 | 8        | 3        | #text         | NULL    | NULL                                      | NULL     | NULL | 3                                         |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+
| 9  | 6        | 3        | #text         | NULL    | NULL                                      | NULL     | 8    | ...                                       |
+----+----------+----------+---------------+---------+-------------------------------------------+----------+------+-------------------------------------------+

您可以使用 parentid 和 nodetype 查找您的 URI:属性 queryNS 的 ID 为 5。这是元素12 的parentid，其中#text 是您要查找的URI。但这不能在即席/内联查询中使用。

更新:您可以使用.query()

SELECT @xml.query(
N'
<root>
{
    for $nd in //*
    return
    <nd ns="{namespace-uri($nd)}" name="{local-name($nd)}" value="{($nd/text())[1]}">
    {
        for $attr in $nd/@*
        return
        <attr ns="{namespace-uri($attr)}" name="{local-name($attr)}" value="{$attr}"  />
    }
    </nd>
}
</root>
');

结果

<root>
  <nd ns="" name="RequestPacket" value="" />
  <nd ns="urn:dont:really:care" name="Requests" value="" />
  <nd ns="urn:dont:really:care" name="Request" value="&#xA;            ...&#xA;        ">
    <attr ns="http://www.w3.org/2001/XMLSchema-instance" name="type" value="queryNS:GetImportantData" />
  </nd>
  <nd ns="urn:uri:i:need" name="Version" value="3" />
</root>