Me and the crickets managed to figure it out....
Had to add the URN for vertex to my namespace declarations:
urn:vertexinc:o-series:tps:7:0
select a.* from pjttest/p13703001 b,
xmltable(
XMLNAMESPACES(
'
http://schemas.xmlsoap.org/soap/envelope/' as "soapenv"
, '
http://www.w3.org/2001/XMLSchema-instance' as "xsi"
, DEFAULT 'urn:vertexinc:o-series:tps:7:0'
),
'$xmldocument/soapenv:Envelope/soapenv:Body/VertexEnvelope/QuotationResponse'
passing b.field1 as "xmldocument"
columns
documentNumber varchar(64) path '@documentNumber'
, documentDate varchar(16) path '@documentDate'
, transactionId varchar(64) path '@transactionId'
, transactionType varchar(16) path '@transactionType'
) as a;
I had not realized that 'URN's are registered as unique namespace objects out in the internet someplace.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Therrien, Paul via MIDRANGE-L
Sent: Thursday, February 8, 2024 10:08 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Therrien, Paul <ptherrien@xxxxxxxxxxx>
Subject: Vertex XML response parsing via xmltable
Going to show my ignorance.
I am trying to process an XML response from Vertex using xmltable.
My sense is that there is probably a namespace I need to reference, but I am unable to determine what it is.
I have loaded the XML into an XML field in a table. Table: P13703001; Field: FIELD1.
This is the SQL I am attempting to use.
select a.* from pjttest/p13703001 b,
xmltable(
XMLNAMESPACES(
'
http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
'
http://www.w3.org/2001/XMLSchema' as "xsd",
'
http://www.w3.org/2001/XMLSchema-instance' as "xsi"
),
'$xmldocument/soapenv:Envelope/soapenv:Header/soapenv:Body/VertexEnvelope/QuotationResponse'
passing b.field1 as "xmldocument"
columns
documentNumber varchar(64) path '@documentNumber'
, documentDate varchar(16) path '@documentDate'
, transactionId varchar(64) path '@transactionId'
, transactionType varchar(16) path '@transactionType'
) as a;
When I try to execute xmltable without XMLNAMESPACES I get an error: SQLSTATE 10506; SQ16005; XPATH expression references undefined name soapenv within static context.'
When I try to execute xmltable on my xml I get SQLSTATE '02000' no records. (but there is a record).
So I assume xmltable is failing but I have no errors, no messages, just sqlstate 02000.
This is the XML I am trying to bring into my program:
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv=
http://schemas.xmlsoap.org/soap/envelope/>
<soapenv:Header/>
<soapenv:Body>
<VertexEnvelope xmlns="urn:vertexinc:o-series:tps:7:0" xmlns:xsi=
http://www.w3.org/2001/XMLSchema-instance>
<Login>
<TrustedId>******</TrustedId>
</Login>
<QuotationResponse documentNumber="73" documentDate="2024-02-10" transactionId="070275714-4000042-20240210-048" transactionType="SALE">
<Currency isoCurrencyCodeAlpha="USD" isoCurrencyCodeNum="840" isoCurrencyName="US Dollar"/>
<Seller>
<Company>mycompany</Company>
<PhysicalOrigin taxAreaId="70010430">
<StreetAddress1>15 Forest Pkwy</StreetAddress1>
<City>Shelton</City>
<MainDivision>CT</MainDivision>
<PostalCode>06484-6137</PostalCode>
<Country>US</Country>
</PhysicalOrigin>
</Seller>
<Customer>
<CustomerCode classCode="Taxable">EP-3043582323</CustomerCode>
<Destination taxAreaId="444711450">
<StreetAddress1>3657 POPPY LN</StreetAddress1>
<City>HUNTSVILLE</City>
<MainDivision>TX</MainDivision>
<PostalCode>77340-8939</PostalCode>
<Country>USA</Country>
</Destination>
</Customer>
<SubTotal>66.9</SubTotal>
<Total>72.25</Total>
<TotalTax>5.35</TotalTax>
<LineItem lineItemNumber="1">
<Product productClass="PB100000">4MU-4000048</Product>
<Quantity>1.0</Quantity>
<FairMarketValue>59.95</FairMarketValue>
<UnitPrice>59.95</UnitPrice>
<ExtendedPrice>59.95</ExtendedPrice>
<Taxes taxResult="TAXABLE" taxType="SELLER_USE" filingCategoryCode="20" situs="DESTINATION" taxCollectedFromParty="BUYER" taxStructure="SINGLE_RATE">
<Jurisdiction jurisdictionLevel="STATE" jurisdictionId="35763">TEXAS</Jurisdiction>
<CalculatedTax>4.79</CalculatedTax>
<EffectiveRate>0.08</EffectiveRate>
<Taxable>59.95</Taxable>
<Imposition impositionId="1">Sales and Use Tax</Imposition>
<ImpositionType impositionTypeId="1">General Sales and Use Tax</ImpositionType>
<TaxRuleId>10704637</TaxRuleId>
</Taxes>
<TotalTax>4.79</TotalTax>
</LineItem>
<LineItem lineItemNumber="2">
<Product productClass="FR">4MU-4000058-SH</Product>
<Quantity>1.0</Quantity>
<FairMarketValue>6.95</FairMarketValue>
<UnitPrice>6.95</UnitPrice>
<ExtendedPrice>6.95</ExtendedPrice>
<Taxes taxResult="TAXABLE" taxType="SELLER_USE" filingCategoryCode="20" situs="DESTINATION" taxCollectedFromParty="BUYER" taxStructure="SINGLE_RATE">
<Jurisdiction jurisdictionLevel="STATE" jurisdictionId="35763">TEXAS</Jurisdiction>
<CalculatedTax>0.56</CalculatedTax>
<EffectiveRate>0.08</EffectiveRate>
<Taxable>6.95</Taxable>
<Imposition impositionId="1">Sales and Use Tax</Imposition>
<ImpositionType impositionTypeId="1">General Sales and Use Tax</ImpositionType>
<TaxRuleId>10282144</TaxRuleId>
</Taxes>
<TotalTax>0.56</TotalTax>
</LineItem>
</QuotationResponse>
<ApplicationData>
<ResponseTimeMS>6.6</ResponseTimeMS>
</ApplicationData>
</VertexEnvelope>
</soapenv:Body>
</soapenv:Envelope>
Any help at all appreciated.
midrange.com
