XMLPARSE

BastienPeyre

Bonjour,

J'ai qques difficultés à parser un XML, je sollicite votre aide

Je joins un exemple de XML à parser.

Voici le XML à parser :

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <searchResponse status="success" xmlns="urn:oasis:names:tc:SPML:2:0:search">
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">organizationalUnit</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Organizational-Unit,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">5u1p++2i0Uqt6RsGpCAiNg==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Tony Stark,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Tony Stark</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">PqSm7jYhskuElPqRvaRQbg==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Tony</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Stark</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">tony.stark</value>
                    </attr>
                    <attr name="mail" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">tony@stark.com</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=ZER_GG_ZENITH_WEB_ACCESS_DELETE_REQUEST,OU=GROUPS,OU=ZER,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">tony.stark</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2660</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBbZAoAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Steve Rogers,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Steve Rogers</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">+ueuEpeceEK6bVSASpnZUg==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Steve</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Rogers</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">steve.rogers</value>
                    </attr>
                    <attr name="mail" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">steve@stark.com</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">steve.rogers</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2814</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBb/goAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Bruce Banner,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Bruce Banner</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">V++VxhOS9kOA2NYRtydvwQ==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Bruce</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Banner</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">bruce.banner</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">bruce.banner</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2815</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBb/woAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Pepper Potts,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Pepper Potts</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">pcoEIk0azk6l9s5c46aVDQ==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Pepper</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Potts</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">pepper.potts</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">pepper.potts</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2816</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBbAAsAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Peter Quill,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Peter Quill</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">wlSVEd5/NkCb5Pkr2Gstcg==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Peter</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Quill</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">peter.quill</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">peter.quill</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2817</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBbAQsAAA==</value>
                    </attr>
                </data>
            </pso>
        </searchResponse>
    </soap:Body>
</soap:Envelope>

Et voici le SQL qui doit parser (ici on appelle un WS mais sa réponse est celle ci dessus) :

Code : Tout sélectionner

SELECT *
     FROM XMLTABLE('$result/*:Envelope/*:Body/*:searchResponse/*:pso'
     PASSING XMLPARSE(
     DOCUMENT 
 SYSTOOLS.HTTPPOSTCLOB(        
  'https://managead.intranet.geodis.org/ARServerSPML/SPMLProvider.asmx',                          
  cast('<httpHeader><header name="Content-Type" value="text/xml"/><header name="Authorization" value="Basic XXXXXXXXXXXXXXXXXXXXXXXXXX="/><header name="Accept" value="text/xml"/></httpHeader>' as clob(1K)),      
  cast('<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <searchRequest maxSelect="100" xmlns="urn:oasis:names:tc:SPML:2:0:search">
      <query scope="subTree">
        <basePsoID ID="OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local"/>
      <attributes xmlns="urn:oasis:names:tc:DSML:2:0:core">
          <attribute name="cn"/>
          <attribute name="middleName"/>
          <attribute name="givenName"/>
          <attribute name="sn"/>
          <attribute name="mail"/>
        </attributes>
    </query>
    </searchRequest>
  </soap12:Body>
</soap12:Envelope>' as clob(1M))         
)                             
) as "result"
COLUMNS 
  id  VARCHAR(250) PATH '*:psoID/@*:ID',
  cn  VARCHAR(200) PATH '*:data/*:attr[1]/*:value'
  givenName  VARCHAR(100) PATH '*:data/*:attr[5]/*:value',
  sn  VARCHAR(100) PATH '*:data/*:attr[6]/*:value',
  middleName  VARCHAR(100) PATH '*:data/*:attr[7]/*:value',
  mail  VARCHAR(100) PATH '*:data/*:attr[8]/*:value'
) AS TABLEXML;

Lorsque dans la réponse il n'y a qu'un élement <pso> cela fonctionne, quand il y en a plusieurs, j'ai une erreur :

Code fournisseur : -16003 Message : [SQ16003] Une expression XPath comporte un type qui est incorrect pour le contexte dans lequel l'expression est utilisée. Cause . . . . . : Une expression de type de données ( item(), item()+ ) ne peut pas être utilisée lorsque le type de données item() est attendu pour le contexte. Les situations suivantes peuvent être à l'origine de cette erreur : -- Pour la fonction de construction d'un type atomique de schéma XML, l'atomisation de son argument ne doit pas produire plusieurs valeurs atomiques. -- Le transtypage d'une valeur de ( item(), item()+ ) vers la cible item() doit être un transtypage pris en charge. -- Une expression XPath était utilisée dans la colonne résultat de fonction de table intégrée XMLTABLE qui ne pouvait pas être transtypée dans le type attendu. Cela se produit généralement lorsque le résultat d'une expression contient plusieurs valeurs atomiques alors qu'un singleton est attendu ; ou bien un transtypage du type de résultat vers le type de données attendu n'est pas pris en charge. L'expression XPath ne peut pas être traitée. Que faire . . . : Indiquez une valeur de type correct. Technical description . . . . . . . . : Error QName=err:XPTY0004.

nbonnet · Message par **nbonnet** » jeu. 23 juil. 2020, 11:47:30

Bonjour,

il y a plusieurs éléments value dans le flux XML résultat.

Un exemple de syntaxe en ne prenant que la première valeur (à adapter donc) ;

Code : Tout sélectionner

SELECT *
     FROM XMLTABLE('$result/*:Envelope/*:Body/*:searchResponse/*:pso'
     PASSING XMLPARSE( document '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <searchResponse status="success" xmlns="urn:oasis:names:tc:SPML:2:0:search">
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">organizationalUnit</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Organizational-Unit,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">5u1p++2i0Uqt6RsGpCAiNg==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Tony Stark,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Tony Stark</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">PqSm7jYhskuElPqRvaRQbg==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Tony</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Stark</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">tony.stark</value>
                    </attr>
                    <attr name="mail" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">tony@stark.com</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=ZER_GG_ZENITH_WEB_ACCESS_DELETE_REQUEST,OU=GROUPS,OU=ZER,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">tony.stark</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2660</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBbZAoAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Steve Rogers,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Steve Rogers</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">+ueuEpeceEK6bVSASpnZUg==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Steve</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Rogers</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">steve.rogers</value>
                    </attr>
                    <attr name="mail" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">steve@stark.com</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">steve.rogers</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2814</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBb/goAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Bruce Banner,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Bruce Banner</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">V++VxhOS9kOA2NYRtydvwQ==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Bruce</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Banner</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">bruce.banner</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">bruce.banner</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2815</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBb/woAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Pepper Potts,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Pepper Potts</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">pcoEIk0azk6l9s5c46aVDQ==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Pepper</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Potts</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">pepper.potts</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">pepper.potts</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2816</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBbAAsAAA==</value>
                    </attr>
                </data>
            </pso>
            <pso xmlns="urn:oasis:names:tc:SPML:2:0">
                <psoID ID="CN=STARK INDUSTRIES - Peter Quill,OU=CUSTOMERS_RCT,DC=customer,DC=geodis,DC=local" />
                <data>
                    <attr name="cn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">STARK INDUSTRIES - Peter Quill</value>
                    </attr>
                    <attr name="objectClass" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">top</value>
                        <value xsi:type="xsd:string">person</value>
                        <value xsi:type="xsd:string">organizationalPerson</value>
                        <value xsi:type="xsd:string">user</value>
                    </attr>
                    <attr name="objectCategory" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=Person,CN=Schema,CN=Configuration,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="objectGUID" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">wlSVEd5/NkCb5Pkr2Gstcg==</value>
                    </attr>
                    <attr name="givenName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Peter</value>
                    </attr>
                    <attr name="sn" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">Quill</value>
                    </attr>
                    <attr name="middleName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">peter.quill</value>
                    </attr>
                    <attr name="memberOf" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">CN=CUS_GL_TMA-ACCOUNTS,OU=GROUPS,OU=CUS,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-TVP,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-PRD,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                        <value xsi:type="xsd:string">CN=GG_SSM_MFA-DEV,OU=GROUPS,OU=SSM,OU=APPLICATIONS,DC=customer,DC=geodis,DC=local</value>
                    </attr>
                    <attr name="sAMAccountName" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">peter.quill</value>
                    </attr>
                    <attr name="userAccountControl" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:int">512</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:string">S-1-5-21-970036446-1441647078-1537226276-2817</value>
                    </attr>
                    <attr name="objectSid" xmlns="urn:oasis:names:tc:DSML:2:0:core">
                        <value xsi:type="xsd:base64Binary">AQUAAAAAAAUVAAAA3pTROebJ7VUkNqBbAQsAAA==</value>
                    </attr>
                </data>
            </pso>
        </searchResponse>
    </soap:Body>
</soap:Envelope>') as "result"
COLUMNS 
  id  VARCHAR(250) PATH '*:psoID/@ID',
  cn  VARCHAR(200) PATH '*:data/*:attr[1]/*:value[1]',
  givenName  VARCHAR(100) PATH '*:data/*:attr[5]/*:value[1]',
  sn  VARCHAR(100) PATH '*:data/*:attr[6]/*:value[1]',
  middleName  VARCHAR(100) PATH '*:data/*:attr[7]/*:value[1]',
  mail  VARCHAR(100) PATH '*:data/*:attr[8]/*:value[1]'
) AS TABLEXML;

BastienPeyre

Ok compris merci.
Dans l'element "Bruce Banner" il n' y a pas d'attribut "mail" donc c'est celui qui contient plusieurs values qui est pris à la place, ce qui ne convient pas.

Existe-il un moyen syntaxique plus propre afin de dire qu'on veut récupérer la value de l'attribut de name "toto" plutôt que par un ID qui risque de mal évoluer ? Et comment préciser de ne pas échouer si la balise n'existe pas (que ça retourne chaine vide) ?

Merci bcp pour votre aide.

BastienPeyre

Ne répondez pas j'ai trouvé

Avec ceci ça marche bcp mieux :

Code : Tout sélectionner

  id  VARCHAR(250) PATH '*:psoID/@*:ID',
  cn  VARCHAR(200) PATH '*:data/*:attr[@name="cn"]/*:value',
  givenName  VARCHAR(100) PATH '*:data/*:attr[@name="givenName"]/*:value',
  sn  VARCHAR(100) PATH '*:data/*:attr[@name="sn"]/*:value',
  middleName  VARCHAR(100) PATH '*:data/*:attr[@name="middleName"]/*:value',
  mail  VARCHAR(100) PATH '*:data/*:attr[@name="mail"]/*:value'

nbonnet · Message par **nbonnet** » jeu. 23 juil. 2020, 14:37:41

Voilà, parfait !

le forum IBM i

XMLPARSE

XMLPARSE

Re: XMLPARSE

Re: XMLPARSE

Re: XMLPARSE

Re: XMLPARSE