I'm trying to parse an XML with different namespaces, I get this xml as a
soap web service response from Netsuite, but I can't get all the values
that I need ...
It is not so clear to me this XPATH syntax!
I need to extract "itemId" and other row information with this SQL
select a.*
FROM
XMLTABLE(
xmlnamespaces (default 'urn:core_2018_1.platform.webservices.netsuite.com',
  '
http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
  'urn:accounting_2018_1.lists.webservices.netsuite.com' as "listact",
  'urn:common_2018_1.platform.webservices.netsuite.com' as "platformCommon"
  ),
'$doc/soap:Envelope/soap:Body/*:searchResponse/searchResult/*:searchRowList/searchRow[*]'
PASSING
xmlparse(document
'<soapenv:Envelope xmlns:soapenv="
http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="
http://www.w3.org/2001/XMLSchema" xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Header>
      <platformMsgs:documentInfo xmlns:platformMsgs="urn:
messages_2018_1.platform.webservices.netsuite.com">
         <platformMsgs:nsId>WEBSERVICES_xxx</platformMsgs:nsId>
      </platformMsgs:documentInfo>
   </soapenv:Header>
   <soapenv:Body>
      <searchResponse xmlns="urn:
core_2018_1.platform.webservices.netsuite.com">
         <searchResult>
            <status isSuccess="true"/>
            <totalRecords>1</totalRecords>
            <pageSize>1000</pageSize>
            <totalPages>1</totalPages>
            <pageIndex>1</pageIndex>
            <searchId>WEBSERVICES_xxx</searchId>
            <searchRowList>
               <searchRow xsi:type="listAcct:ItemSearchRow"
xmlns:listAcct="urn:accounting_2018_1.lists.webservices.netsuite.com">
                  <listAcct:basic xmlns:platformCommon="urn:
common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon:displayName>
                        <searchValue>YYY</searchValue>
                     </platformCommon:displayName>
                     <platformCommon:internalId>
                        <searchValue internalId="661"/>
                     </platformCommon:internalId>
                     <platformCommon:itemId>
                        <searchValue>YYY2</searchValue>
                     </platformCommon:itemId>
                     <platformCommon:saleUnit>
                        <searchValue internalId="4"/>
                     </platformCommon:saleUnit>
                     <platformCommon:type>
                        <searchValue>_nonInventoryItem</searchValue>
                     </platformCommon:type>
                     <platformCommon:weight>
                        <searchValue>0.0</searchValue>
                     </platformCommon:weight>
                     <platformCommon:customFieldList>
                        <customField internalId="2595" scriptId="yyy3"
xsi:type="ns1:SearchColumnLongCustomField" xmlns:ns1="urn:
core_2018_1.platform.webservices.netsuite.com">
                           <ns1:searchValue>12</ns1:searchValue>
                        </customField>
                     </platformCommon:customFieldList>
                  </listAcct:basic>
                  <listAcct:customSearchJoin xmlns:platformCommon="urn:
common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon:customizationRef scriptId="yyy3"
internalId="6666"/>
                     <platformCommon:searchRowBasic
xsi:type="platformCommon:CustomRecordSearchRowBasic">
                        <platformCommon:recType internalId="666"/>
                        <platformCommon:name>
                           <searchValue>TEST</searchValue>
                           <customLabel>Class</customLabel>
                        </platformCommon:name>
                     </platformCommon:searchRowBasic>
                  </listAcct:customSearchJoin>
                  <listAcct:customSearchJoin xmlns:platformCommon="urn:
common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon:customizationRef scriptId="xxx"
internalId="6666"/>
                     <platformCommon:searchRowBasic
xsi:type="platformCommon:CustomRecordSearchRowBasic">
                        <platformCommon:recType internalId="666"/>
                        <platformCommon:name>
                           <searchValue>TESTID</searchValue>
                           <customLabel>Packaging</customLabel>
                        </platformCommon:name>
                     </platformCommon:searchRowBasic>
                  </listAcct:customSearchJoin>
                  <listAcct:customSearchJoin xmlns:platformCommon="urn:
common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon:customizationRef scriptId="test2"
internalId="6666"/>
                     <platformCommon:searchRowBasic
xsi:type="platformCommon:CustomRecordSearchRowBasic">
                        <platformCommon:recType internalId="666"/>
                        <platformCommon:name>
                           <searchValue>1234567890123</searchValue>
                           <customLabel>Barcode</customLabel>
                        </platformCommon:name>
                     </platformCommon:searchRowBasic>
                  </listAcct:customSearchJoin>
               </searchRow>
            </searchRowList>
         </searchResult>
      </searchResponse>
   </soapenv:Body>
</soapenv:Envelope>'
)
as "doc"
columns
totalRecords decimal(9, 0) path '../../totalRecords',
pageSize decimal(9, 0) path '../../pageSize',
totalPages decimal(9, 0) path '../../totalPages',
pageIndex decimal(9, 0) path '../../pageIndex',
searchId varchar(50) path '../../searchId'
,displayName varchar(50) path 'basic/displayName/searchValue'
,itemId varchar(50) path 'basic/itemId/searchValue'
) as a;
And this is what I get... null for "displayName" and "itemId"
TOTALRECORPAGESIZE  TOTALPAGESPAGEINDEX SEARCHID
               DISPLAYNAME                                       ITEMID
 1         1000      1         1        WEBSERVICES_xxx
Thank you all
By from Italy
Roberto
As an Amazon Associate we earn from qualifying purchases.