FutureDBA-
asked on
XML Query - Oracle
Hi All, a while back I was helped on here with an XML query. The Query works but I am having trouble querying out a new object (signature URL)
The query returns all the required information, except not on the URL in signatureURL.
This is the Query.
This is the XML
The query returns all the required information, except not on the URL in signatureURL.
This is the Query.
SELECT DISTINCT
c.taxid,
CASE WHEN c.taxid != '0' OR LENGTH(c.id) = 5 THEN c.taxid ELSE REGEXP_SUBSTR(c.id, '^[^- ]+') END chain,
CASE WHEN LENGTH(c.id) = 5 THEN c.id ELSE REGEXP_SUBSTR(c.id, '[^- ]+$') END customer,
CASE WHEN c.taxid is null then '0' else c.taxid end as taxid,
c.mtime,
o.owner,
o.signature,
o.sku,
o.qty
FROM xml_data_tab t,
XMLTABLE(
'//customer'
PASSING t.xml_data
COLUMNS cust_seq FOR ORDINALITY,
id VARCHAR2(50) PATH '/customer/id',
taxid VARCHAR2(50) PATH '/customer/taxID',
mtime VARCHAR2(50) PATH '/customer/billTo/mtime'
) c,
XMLTABLE(
'//lines'
PASSING t.xml_data
COLUMNS line_seq FOR ORDINALITY, node XMLTYPE PATH '.'
) l,
XMLTABLE(
'//object'
PASSING l.node
COLUMNS
signature VARCHAR2(100) PATH '/object/signatureURL',
owner VARCHAR2(50) PATH '/object/owner',
sku VARCHAR2(50) PATH '/object/sku',
qty VARCHAR2(50) PATH '/object/qty'
) o
WHERE l.line_seq = c.cust_seq
This is the XML
<object type="hash">
<otherVersions type="list"/>
<percentDiscount type="null"/>
<entityType>SalesOrder</entityType>
<ownerName>John test</ownerName>
<mtime>2014-09-15T16:12:06Z</mtime>
<owner>johnt@test.com</owner>
<promoDetails type="null"/>
<shippingMethod>FEDEX</shippingMethod>
<id>#1140021</id>
<category>/api/v3/order_categories/4960</category>
<realVersionID type="null"/>
<paymentTerms>PREPAID</paymentTerms>
<uuid>da6ca3b0-7dcf-46b8-9ee7-c462fead60f4</uuid>
<ctimeOnServer>2014-09-15T16:12:06Z</ctimeOnServer>
<freeShipping type="boolean">False</freeShipping>
<realVersion type="null"/>
<startShipDate type="null"/>
<versionType type="null"/>
<mtimeOnServer>2014-09-15T16:12:31Z</mtimeOnServer>
<shipPartial type="boolean">True</shipPartial>
<userGroup>/api/v3/user_groups/2</userGroup>
<totalAmount>131.88</totalAmount>
<subtotalAmount>131.88</subtotalAmount>
<status>Open</status>
<customer type="hash">...</customer>
<shipTo type="hash">...</shipTo>
<defaultWarehouse>/api/v3/warehouses/158611</defaultWarehouse>
<sourceType>seller</sourceType>
<billTo type="hash">...</billTo>
<creditCard type="null"/>
<lastExported type="null"/>
<cdate>2014-09-15</cdate>
<photoURLs type="list"/>
<versionInfo type="null"/>
<signatureURL>
https://app.test.com/tempFile/b81f73fd-e09d-4786-9459-7976d49d7bb4-signature-1140021-1cfb47a5cd88f397e70b9e46b1d6422b08800f3b.png
</signatureURL>
<shippedStatus>Not Shipped</shippedStatus>
<priceLists type="list"/>
<totalOffDiscount type="null"/>
<ctime>2014-09-15T16:11:39Z</ctime>
<notes/>
<promoCode type="null"/>
<lines type="list">...</lines>
<cancelDate type="null"/>
<objID type="integer">1140021</objID>
<lastEdited>2014-09-15T16:12:31Z</lastEdited>
<customerPO type="null"/>
<realVersionUUID type="null"/>
<resource_uri>/api/v3/orders/1140021</resource_uri>
</object>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT DISTINCT
c.taxid,
CASE
WHEN c.taxid != '0' OR LENGTH(c.id) = 5 THEN c.taxid
ELSE REGEXP_SUBSTR(c.id, '^[^- ]+')
END
chain,
CASE WHEN LENGTH(c.id) = 5 THEN c.id ELSE REGEXP_SUBSTR(c.id, '[^- ]+$') END customer,
CASE WHEN c.taxid IS NULL THEN '0' ELSE c.taxid END AS taxid,
c.mtime,
c.owner,
c.signature,
c.sku,
c.qty
FROM xml_data_tab t,
XMLTABLE(
'/object'
PASSING t.xml_data
COLUMNS cust_seq FOR ORDINALITY,
id VARCHAR2(50) PATH '/object/customer/id',
taxid VARCHAR2(50) PATH '/object/customer/taxID',
mtime VARCHAR2(50) PATH '/object/customer/billTo/m
signature VARCHAR2(100) PATH '/object/signatureURL',
owner VARCHAR2(50) PATH '/object/owner',
sku VARCHAR2(50) PATH '/object/sku',
qty VARCHAR2(50) PATH '/object/qty'
) c,
XMLTABLE(
'//lines'
PASSING t.xml_data
COLUMNS line_seq FOR ORDINALITY, node XMLTYPE PATH '.'
) l
WHERE l.line_seq = c.cust_seq