xml to sql with openxml

я хотел бы консультироваться с sql, этот xml и смог давать результат таблицы поля, которые внутри Body:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope"
    xmlns:e="http://www.technisys.net/cmm/services/errors/v1.0" xmlns:md="http://www.technisys.net/cmm/services/metadata/v2.0"
    xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
    xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <SOAP-ENV:Header>
     <md:metadata><traceNumber>191750</traceNumber>
         <serviceId>processSystemUserLoginByCustomerMigration</serviceId>
         <serviceVersion>0.0</serviceVersion>
         <sessionId>OAUTH2_58f93b26-f876-42ab-aa41-2c9d482a199f_rriosc@bancofalabella.com.pe</sessionId>
         <targetChannel>
             <mnemonic>PF</mnemonic>
         </targetChannel>
         <executingChannel>
             <mnemonic>PF</mnemonic>
         </executingChannel>
         <address>ip=10.255.0.2;</address>
         <sourceDate>20190201101319013</sourceDate>
         <channelDispatchDate>xxxxxxxxxxxx</channelDispatchDate>
         <locale>es_AR</locale>
         <featureId>ROL@4702</featureId>
         <institutionType>UNDEFINED</institutionType>
         <msgTypeId>200</msgTypeId>
         <terminalId>browser=Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36;platform=;subChannel=;terminalCode=;</terminalId>
         <organizationId></organizationId>
         <organizationOperatorId>uriTech=OPERADOR_BANCO@9884015;uriSFB=150059641;branchCode=209;execUser=rriosc@bancofalabella.com.pe;execCodePosition=4;execNamePosition=EECC;execCodeRole=3;execNameRole=Ejecutivos Comerciales;</organizationOperatorId>
         <executingOperatorId>uriTech=OPERADOR_BANCO@9884015;uriSFB=150059641;branchCode=209;execUser=rriosc@bancofalabella.com.pe;execCodePosition=4;execNamePosition=EECC;execCodeRole=3;execNameRole=Ejecutivos Comerciales;</executingOperatorId>
         <paginationInfo></paginationInfo>
         <branchId>1</branchId>
         <userId></userId>
         <parityCurrencyId />
         <localCurrencyId />
         <localCountryId />
         <bankId />
         <organizationType/>
     </md:metadata>

    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
     <srv:processSystemUserLoginByCustomerMigrationRequest xmlns:srv="http://www.technisys.net/cmm/services/processSystemUserLoginByCustomerMigration/rq/v0.0">
     <generic><sessionId type="java.lang.String">OAUTH2_58f93b26-f876-42ab-aa41-2c9d482a199f_rriosc@bancofalabella.com.pe</sessionId><forceToAdhere type="java.lang.Boolean">false</forceToAdhere></generic><customer name="customer"><identificationNumber>12345678</identificationNumber><identificationType><mnemonic>DNI</mnemonic></identificationType></customer>
     </srv:processSystemUserLoginByCustomerMigrationRequest>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Моя таблица, оказанный должна бы быть identificationNumber | identificationType 12345678 удостоверений личности

0
задан 15.03.2019, 19:08
1 ответ

Ты можешь реализовывать это следующего способа, где @info - стоимость твоего XML., если ты нуждаешься в большей опоре для нее interpretaciГіn узлов XML в SQL Server, проверь следующий соединение :

DECLARE @info XML = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope"
    xmlns:e="http://www.technisys.net/cmm/services/errors/v1.0" xmlns:md="http://www.technisys.net/cmm/services/metadata/v2.0"
    xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
    xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <SOAP-ENV:Header>
     <md:metadata><traceNumber>191750</traceNumber>
         <serviceId>processSystemUserLoginByCustomerMigration</serviceId>
         <serviceVersion>0.0</serviceVersion>
         <sessionId>OAUTH2_58f93b26-f876-42ab-aa41-2c9d482a199f_rriosc@bancofalabella.com.pe</sessionId>
         <targetChannel>
             <mnemonic>PF</mnemonic>
         </targetChannel>
         <executingChannel>
             <mnemonic>PF</mnemonic>
         </executingChannel>
         <address>ip=10.255.0.2;</address>
         <sourceDate>20190201101319013</sourceDate>
         <channelDispatchDate>xxxxxxxxxxxx</channelDispatchDate>
         <locale>es_AR</locale>
         <featureId>ROL@4702</featureId>
         <institutionType>UNDEFINED</institutionType>
         <msgTypeId>200</msgTypeId>
         <terminalId>browser=Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36;platform=;subChannel=;terminalCode=;</terminalId>
         <organizationId></organizationId>
         <organizationOperatorId>uriTech=OPERADOR_BANCO@9884015;uriSFB=150059641;branchCode=209;execUser=rriosc@bancofalabella.com.pe;execCodePosition=4;execNamePosition=EECC;execCodeRole=3;execNameRole=Ejecutivos Comerciales;</organizationOperatorId>
         <executingOperatorId>uriTech=OPERADOR_BANCO@9884015;uriSFB=150059641;branchCode=209;execUser=rriosc@bancofalabella.com.pe;execCodePosition=4;execNamePosition=EECC;execCodeRole=3;execNameRole=Ejecutivos Comerciales;</executingOperatorId>
         <paginationInfo></paginationInfo>
         <branchId>1</branchId>
         <userId></userId>
         <parityCurrencyId />
         <localCurrencyId />
         <localCountryId />
         <bankId />
         <organizationType/>
     </md:metadata>
    </SOAP-ENV:Header>

    <SOAP-ENV:Body>
     <srv:processSystemUserLoginByCustomerMigrationRequest xmlns:srv="http://www.technisys.net/cmm/services/processSystemUserLoginByCustomerMigration/rq/v0.0">
        <generic>
            <sessionId type="java.lang.String">OAUTH2_58f93b26-f876-42ab-aa41-2c9d482a199f_rriosc@bancofalabella.com.pe</sessionId>
            <forceToAdhere type="java.lang.Boolean">false</forceToAdhere>
        </generic>
        <customer name="customer">
            <identificationNumber>12345678</identificationNumber>
            <identificationType>
                <mnemonic>DNI</mnemonic>
            </identificationType>
        </customer>
    </srv:processSystemUserLoginByCustomerMigrationRequest>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>'


SELECT r.value('identificationNumber[1]','int') AS identificationNumber
      ,r.value('identificationType[1]','varchar(max)') AS identificationType      
FROM @info.nodes('/*:Envelope/*:Body/*:processSystemUserLoginByCustomerMigrationRequest/customer') AS A(r)
0
ответ дан 02.12.2019, 05:35

Теги

Похожие вопросы