时间戳

首页 > 解决方案 > 使用 SQL 获取 XML 节点中的值

sql - 使用 SQL 获取 XML 节点中的值

问题描述

我在 SQL 表列中有一个 XML。我需要解码这个 xml 并获取特定节点的值。在下面找到我的 XML

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Header/>
    <SOAP-ENV:Body>
        <ns:createTransactionResponse
            xmlns:impl="http://office/work/services/service1"
            xmlns:ns="http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd"
            xmlns:tns="http://www.erdeftq.ae/Activematrix/ESB/service1/1_0">
            <transactionResponse>
                <transaction-info>
                    <registrationId>R1234</registrationId>
                    <trialId>T12345</trialId>
                    <transactionId>12345</transactionId>
                    <transactionDate>27-02-2020:08:47</transactionDate>
                    <status>Confirmed</status>
                </transaction-info>
            </transactionResponse>
        </ns:createTransactionResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

我需要节点的值：registrationId、transactionId 和 status，我尝试了这个但没有成功，因为我得到了空值：

DECLARE @xml XML
SET @xml = 'XML here'
SELECT T.C.value('@status', 'nvarchar(100)') FROM @xml.nodes('createTransactionResponse/transactionResponse/transaction-info/status') T(C)
SELECT T.C.value('@trans', 'nvarchar(100)') FROM @xml.nodes('createTransactionResponse/transactionResponse/transaction-info/transactionId') T(C)
SELECT T.C.value('@id', 'nvarchar(100)') FROM @xml.nodes('createTransactionResponse/transactionResponse/transaction-info/registrationId') T(C)

任何帮助/更正将不胜感激

标签： sqlsql-serverxml

解决方案

您自己的尝试是忽略名称空间并且没有指定完整的 XPath。

尝试以下方法之一：

你的 XML：

DECLARE @xml XML
SET @xml = '<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Header/>
    <SOAP-ENV:Body>
        <ns:createTransactionResponse
            xmlns:impl="http://office/work/services/service1"
            xmlns:ns="http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd"
            xmlns:tns="http://www.erdeftq.ae/Activematrix/ESB/service1/1_0">
            <transactionResponse>
                <transaction-info>
                    <registrationId>R1234</registrationId>
                    <trialId>T12345</trialId>
                    <transactionId>12345</transactionId>
                    <transactionDate>27-02-2020:08:47</transactionDate>
                    <status>Confirmed</status>
                </transaction-info>
            </transactionResponse>
        </ns:createTransactionResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';

--这是最明确的（这始终是最好的方式）：

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS n1
                  ,'http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd' AS n2)
SELECT @xml.value('(/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info/registrationId/text())[1]','nvarchar(max)') AS RegistrationId
      ,@xml.value('(/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info/transactionId/text())[1]','nvarchar(max)') AS TransactionId
      ,@xml.value('(/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info/status/text())[1]','nvarchar(max)') AS [Status];

-- 这将避免一些重复的 XPath，但.nodes()会产生相当多的开销：

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS n1
                  ,'http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd' AS n2)
SELECT ti.value('(registrationId/text())[1]','nvarchar(max)') AS RegistrationId
      ,ti.value('(transactionId/text())[1]','nvarchar(max)') AS TransactionId
      ,ti.value('(status/text())[1]','nvarchar(max)') AS [Status]
FROM @xml.nodes('/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info') A(ti);

--这是给懒惰的人的:-)

SELECT @xml.value('(//*:registrationId)[1]','nvarchar(max)') AS RegistrationId
      ,@xml.value('(//*:transactionId)[1]','nvarchar(max)') AS TransactionId
      ,@xml.value('(//*:status)[1]','nvarchar(max)') AS [Status];

提示：最后一个（对于懒惰的人）使用深度搜索（with //）并为命名空间使用通配符。如果元素可能在您的 XML 中多次出现，这是非常危险的。

推荐阅读