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
midrange.com
