sql-server - XPath 查询在在线 XPath 测试器中运行良好，但在 SQL Server 中却不行

Question

我有一段 XML 必须在 SQL Server 中分解。到目前为止，我已经部分成功，但尽管我在网上找到了几个小时的代码并尝试了这些代码，但我没有取得进一步的进展......

在 SQL 代码下方，包括一段缩减的 XML:

DECLARE @xml xml, @hdoc int
SET @xml = '
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://www.egem.nl/StUF/StUF0301" xmlns:ns2="http://www.egem.nl/StUF/sector/zkn/0310" xmlns:bg="http://www.egem.nl/StUF/sector/bg/0310">
  <SOAP-ENV:Body>
    <ns2:zakLk01>
      <ns2:object ns1:entiteittype="ZAK" ns1:verwerkingssoort="T">
        <ns2:heeftBetrekkingOp ns1:entiteittype="ZAKOBJ" ns1:verwerkingssoort="T">
          <ns2:gerelateerde>
            <ns2:natuurlijkPersoon ns1:entiteittype="NPS" ns1:verwerkingssoort="I">
              <verblijfsadres xmlns="http://www.egem.nl/StUF/sector/bg/0310">
                <gor.openbareRuimteNaam>Westmalledreef 45</gor.openbareRuimteNaam>
                <wpl.woonplaatsNaam>B-16753</wpl.woonplaatsNaam>
              </verblijfsadres>
              <voornamen xmlns="http://www.egem.nl/StUF/sector/bg/0310">Erik</voornamen>
            </ns2:natuurlijkPersoon>
          </ns2:gerelateerde>
        </ns2:heeftBetrekkingOp>
      </ns2:object>
    </ns2:zakLk01>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
'

EXEC sp_xml_preparedocument 
@hDoc OUTPUT, 
@XML,
'<root xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:ns1="http://www.egem.nl/StUF/StUF0301" 
    xmlns:ns2="http://www.egem.nl/StUF/sector/zkn/0310"
    xmlns:bg="http://www.egem.nl/StUF/sector/bg/0310>"/>'


SELECT * FROM OPENXML(@hdoc, '/SOAP-ENV:Envelope') --Row Pattern
WITH 
(
Voornamen VARCHAR(50) './/ns2:heeftBetrekkingOp/ns2:gerelateerde/ns2:natuurlijkPersoon/bg:voornamen',
Adres VARCHAR(100) './/ns2:heeftBetrekkingOp/ns2:gerelateerde/ns2:natuurlijkPersoon/bg:verblijfsadres/bg:gor.openbareRuimteNaam'
)

EXEC sp_xml_removedocument @hdoc --Releasing memory

当我在 https://www.freeformatter.com/xpath-tester.html 上尝试两个 XPath 查询时使用相同的 XML，它们可以正常工作，并将“voornamen”和“openbareRuimteNaam”的值返回给我。在 SQL 中，无论我尝试什么，它们都会返回 NULL。我一定做错了什么。谁能指出我正确的方向？

我已经尝试过 SQL Server 2014 和 2016。

Answer 1

您可以使用 .value方法而不是 OPENXML .

示例解决方案:

;WITH XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS [SOAP-ENV],
'http://www.egem.nl/StUF/StUF0301' AS ns1,
'http://www.egem.nl/StUF/sector/zkn/0310' AS ns2,
'http://www.egem.nl/StUF/sector/bg/0310' AS bg
)

SELECT @xml.value('(//bg:voornamen/text())[1]','VARCHAR(50)') AS Voornamen,
@xml.value('(//bg:gor.openbareRuimteNaam/text())[1]','VARCHAR(100)') AS Adres

当然是深度搜索 //不是好习惯。最好指定每个节点号:

;WITH XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS [SOAP-ENV],
'http://www.egem.nl/StUF/StUF0301' AS ns1,
'http://www.egem.nl/StUF/sector/zkn/0310' AS ns2,
'http://www.egem.nl/StUF/sector/bg/0310' AS bg
)

SELECT @xml.value('(SOAP-ENV:Envelope[1]/SOAP-ENV:Body[1]/ns2:zakLk01[1]/ns2:object[1]/ns2:heeftBetrekkingOp[1]/ns2:gerelateerde[1]/ns2:natuurlijkPersoon[1]/bg:voornamen[1]/text())[1]','VARCHAR(50)') AS Voornamen,
@xml.value('(SOAP-ENV:Envelope[1]/SOAP-ENV:Body[1]/ns2:zakLk01[1]/ns2:object[1]/ns2:heeftBetrekkingOp[1]/ns2:gerelateerde[1]/ns2:natuurlijkPersoon[1]/bg:verblijfsadres[1]/bg:gor.openbareRuimteNaam[1]/text())[1]','VARCHAR(100)') AS Adres