La requête XPath fonctionne correctement dans le testeur XPath en ligne mais pas dans SQL Server

Question

J'ai un morceau de XML que je dois détruire dans SQL Server. Jusqu'à présent, j'ai partiellement réussi, mais je n'avance pas malgré des heures de recherche et d'essai du code que j'ai trouvé en ligne...

Ci-dessous, le code SQL, y compris une partie réduite du 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

Lorsque j'essaie les deux requêtes XPath sur https://www.freeformatter.com/xpath-tester.html en utilisant le même morceau de XML, ils fonctionnent correctement et me renvoient les valeurs "voornamen" et "openbareRuimteNaam". En SQL, ils renvoient NULL quoi que j'essaie. Je dois faire quelque chose de mal. Quelqu'un peut-il m'indiquer la bonne direction ?

J'ai essayé SQL Server 2014 et 2016.

Answer 1

Vous pouvez utiliser .value au lieu de la méthode OPENXML .

Exemple de solution :

;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

Bien entendu, la recherche approfondie // n'est pas une bonne pratique. Il serait préférable de spécifier chaque numéro de nœud :

;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

Answer 2

La raison

Comment ce XML est-il généré ? Le problème que vous rencontrez se trouve ici :

<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>

Vous déclarez de nouvelles espaces de noms par défaut avec la même URL que pour vos autres espaces de noms.

Dans le code suivant, j'ai supprimé ces espaces de noms supplémentaires et j'utilise votre code avec une toute petite modification : Pas de préfixe pour voornamen y gor.openbareRuimteNaam . Cela fonctionne :

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>
                <gor.openbareRuimteNaam>Westmalledreef 45</gor.openbareRuimteNaam>
                <wpl.woonplaatsNaam>B-16753</wpl.woonplaatsNaam>
              </verblijfsadres>
              <voornamen>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/voornamen',
Adres VARCHAR(100) './/ns2:heeftBetrekkingOp/ns2:gerelateerde/ns2:natuurlijkPersoon/verblijfsadres/gor.openbareRuimteNaam'
)

EXEC sp_xml_removedocument @hdoc --Releasing memory

Analyser votre XML

Essayez votre requête originale sans le WITH comme ceci

SELECT * FROM OPENXML(@hdoc, '/SOAP-ENV:Envelope')

Et vous obtiendrez une liste, comment le moteur XML voit vos éléments (fragment) :

+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| natuurlijkPersoon      | ns2   | http://www.egem.nl/StUF/sector/zkn/0310 | NULL | NULL | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| entiteittype           | ns1   | http://www.egem.nl/StUF/StUF0301        | NULL | NULL | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| #text                  | NULL  | NULL                                    | NULL | NULL | NPS                                    |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| verwerkingssoort       | ns1   | http://www.egem.nl/StUF/StUF0301        | NULL | NULL | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| #text                  | NULL  | NULL                                    | NULL | NULL | I                                      |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| verblijfsadres         | NULL  | http://www.egem.nl/StUF/sector/bg/0310  | NULL | NULL | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| xmlns                  | xmlns | NULL                                    | NULL | NULL | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| #text                  | NULL  | NULL                                    | NULL | NULL | http://www.egem.nl/StUF/sector/bg/0310 |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| gor.openbareRuimteNaam | NULL  | http://www.egem.nl/StUF/sector/bg/0310  | NULL | NULL | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| #text                  | NULL  | NULL                                    | NULL | NULL | Westmalledreef 45                      |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| wpl.woonplaatsNaam     | NULL  | http://www.egem.nl/StUF/sector/bg/0310  | NULL | 20   | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| #text                  | NULL  | NULL                                    | NULL | NULL | B-16753                                |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+
| voornamen              | NULL  | http://www.egem.nl/StUF/sector/bg/0310  | NULL | 18   | NULL                                   |
+------------------------+-------+-----------------------------------------+------+------+----------------------------------------+

Vous pouvez voir que les éléments que vous recherchez vivent dans un espace de noms, mais n'ont pas de préfixe...

Tout ceci n'est qu'une explication de la raison pour laquelle votre requête ne fonctionne pas. Mais.. :

Vous devriez adopter une autre approche !

FROM OPENXML avec les PS correspondants pour préparer et retirer un document est dépassée et ne doit plus être utilisée (à de rares exceptions près). Il convient plutôt d'utiliser le les méthodes proposées par le type de données XML .

Soyez aussi explicite que possible :

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

C'est simple et laid

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