-
1. Re: Querying CLOB column (with XML content)
BluShadow Sep 2, 2014 7:33 AM (in response to Veera_V)Your example data is rather basic so there's not much to demonstrate. However, you don't need to know the order of the XML data, as the tag names identify the data, regardless of the order they are provided in the XML. It CAN make a difference if there are repeated elements and you want to pick them out in order, but your example data doesn't indicate such a need.
SQL> ed
Wrote file afiedt.buf1 with t as (select '<REQUEST_DETAIL>
2 <GROUP_TYPE>PR</GROUP_TYPE>
3 <GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
4 <BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
5 <PROJ_MGR_ID></PROJ_MGR_ID>
6 <PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
7 </REQUEST_DETAIL>' as clob_data from dual)
8 --
9 -- end of test data
10 --
11 select x.*
12 from t
13 ,xmltable('/'
14 passing xmltype(t.clob_data)
15 columns group_type varchar2(2) path './GROUP_TYPE'
16 ,group_name varchar2(30) path './GROUP_NAME'
17 ,bus_unit_acrn varchar2(10) path './BUS_UNIT_ACRN'
18 ,proj_mgr_id number path './PROJ_MGR_ID'
19 ,proj_mgr_name varchar2(20) path './PROJ_MGR_NAME'
20* ) x
SQL> /GR GROUP_NAME BUS_UNIT_A PROJ_MGR_ID PROJ_MGR_NAME
-- ------------------------------ ---------- ----------- --------------------
PR DATA ENGINEERING ASSET MANAGEM SCS-FCAT Roland RoyNotes:
a) you are better storing your XML in XMLTYPE on the database rather than CLOB
b) you should avoid using extract and getStringVal (or getClobVal) functions as these are old and deprecated, better to use XMLTABLE or XQuery functionality provided since 10g and 11g respectively.
c) don't use LOB/String functions to process XML data.
-
2. Re: Querying CLOB column (with XML content)
Veera_V Sep 2, 2014 9:53 AM (in response to BluShadow)Hi BluShadow,
I am getting null value when I ran below:
SELECT X.*
from t
,xmltable('/'
PASSING XMLTYPE(t.XML_BLOB)
columns DomainName varchar2(15) path './DomainName'
) X
WHERE t.no=317229340
From XML_BLOB (column CLOB type):-
..
..
<DomainName>P099</DomainName>
..
..
Regards,
Veera
-
3. Re: Querying CLOB column (with XML content)
Moazzam Sep 2, 2014 9:58 AM (in response to Veera_V)See below example
SELECT dbms_xmlgen.convert(x.description.getClobVal(), 1) as description
FROM test_xml t
, XMLTable(
'/*/record'
passing t.xml_data
columns
description xmltype path 'description/text()'
) x
;Oracle SQL - Extracting clob value from XML with repeating nodes
-
4. Re: Querying CLOB column (with XML content)
Veera_V Sep 2, 2014 10:01 AM (in response to Veera_V)Seems this <DomainName>P099</DomainName> comes repatedly for multiple elements . (i need unique of single domain name just one P099 and ignore domains "PUB" in the same clob data).
-
5. Re: Re: Querying CLOB column (with XML content)
odie_63 Sep 2, 2014 10:11 AM (in response to Veera_V)Veera,
What's your database version? (SELECT * FROM v$version)
Why do you not know the paths to query?
If you want to extract data from a specific node regardless of its place, use a descendant axis (//) :
SELECT X.*
from t
, xmltable('.'
PASSING XMLTYPE(t.XML_BLOB)
columns DomainName varchar2(15) path '//DomainName'
) X
but be aware it's not a recommended way since Oracle has to walk down the entire tree to find out the target node, which could lead to serious performance issues.
If you know the complete path from root then by all means use it.
-
6. Re: Re: Querying CLOB column (with XML content)
odie_63 Sep 2, 2014 10:14 AM (in response to Veera_V)2729533 wrote:
Seems this <DomainName>P099</DomainName> comes repatedly for multiple elements . (i need unique of single domain name just one P099 and ignore domains "PUB" in the same clob data).
Post a valid sample XML please, and explain clearly what result you want.
Thanks.
-
7. Re: Querying CLOB column (with XML content)
Veera_V Sep 2, 2014 11:46 AM (in response to odie_63)Database version is 11.2.0.3
single xml will have multiple transactions (multiple elements and they will be loaded into different related tables ).
-
8. Re: Querying CLOB column (with XML content)
BluShadow Sep 2, 2014 12:15 PM (in response to Veera_V)To repeat what odie asked...
Post a valid sample XML please
-
9. Re: Querying CLOB column (with XML content)
Veera_V Sep 3, 2014 1:57 AM (in response to BluShadow)From the below xml content I need only domainname value (not required to search entire xml - 1st occurance of domainname . here P099).
below query works for less no of inputs.
select DBMS_LOB.SUBSTR (XML_BLOB, 4, DBMS_LOB.INSTR (XML_BLOB, '<DomainName>', 1, 1 ) + 12 )
from t_tab
WHERE t_tab=317663815
(If I for entire table , it goes to wait state for accesing LOB index and never return rows;this table is live and continuously using the XML content to processs).
<?xml version="1.0" encoding="UTF-8"?>
<Transmission>
<TransmissionHeader>
<UserName>P016.INT1</UserName>
<Password>INT1</Password>
<SenderTransmissionNo>P099_03092014021310_05081733000240</SenderTransmissionNo>
<AckSpec>
<ComMethodGid>
<Gid>
<Xid>EMAIL</Xid>
</Gid>
</ComMethodGid>
<AckOption>ERROR</AckOption>
</AckSpec>
</TransmissionHeader>
<TransmissionBody>
<GLogXMLElement>
<Location>
<TransactionCode>IU</TransactionCode>
<LocationGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>969000545</Xid>
</Gid>
</LocationGid>
<LocationName>KLA TENCOR</LocationName>
<Address>
<AddressLine1>32 CH DU VIEUX CHENE</AddressLine1>
<AddressLine2>38240 MEYLAN</AddressLine2>
<City>MEYLAN</City>
<PostalCode>38240</PostalCode>
<CountryCode3Gid>
<Gid>
<Xid>FR</Xid>
</Gid>
</CountryCode3Gid>
<TimeZoneGid>
<Gid>
<Xid>Local</Xid>
</Gid>
</TimeZoneGid>
</Address>
<Contact>
<ContactGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>969000545</Xid>
</Gid>
</ContactGid>
<FirstName>KLA TENCOR</FirstName>
</Contact>
<ParentLocationGid>
<LocationGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>P099</Xid>
</Gid>
</LocationGid>
</ParentLocationGid>
</Location>
</GLogXMLElement>
<GLogXMLElement>
<Location>
<TransactionCode>IU</TransactionCode>
<LocationGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>CONSIGNEE-9979028275</Xid>
</Gid>
</LocationGid>
<LocationName>KLA TENCOR</LocationName>
<Address>
<AddressLine1>159 OLD WOOD PLACE</AddressLine1>
<AddressLine2>ELIBURN - LIVINGSTON</AddressLine2>
<AddressLines>
<SequenceNumber>3</SequenceNumber>
<AddressLine>GREAT BRITAIN</AddressLine>
</AddressLines>
<City>ELIBURN - LIVINGSTON</City>
<PostalCode>1101</PostalCode>
<CountryCode3Gid>
<Gid>
<Xid>XL</Xid>
</Gid>
</CountryCode3Gid>
<TimeZoneGid>
<Gid>
<Xid>Local</Xid>
</Gid>
</TimeZoneGid>
</Address>
<Contact>
<ContactGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>CONSIGNEE-9979028275</Xid>
</Gid>
</ContactGid>
<FirstName>KLA TENCOR</FirstName>
</Contact>
<ParentLocationGid>
<LocationGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>P099</Xid>
</Gid>
</LocationGid>
</ParentLocationGid>
</Location>
</GLogXMLElement>
<GLogXMLElement>
<Location>
<TransactionCode>IU</TransactionCode>
<LocationGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>PAYEE-969000545</Xid>
</Gid>
</LocationGid>
<Address>
<CountryCode3Gid>
<Gid>
<Xid>ZZZ</Xid>
</Gid>
</CountryCode3Gid>
<TimeZoneGid>
<Gid>
<Xid>Local</Xid>
</Gid>
</TimeZoneGid>
</Address>
<Contact>
<ContactGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>PAYEE-969000545</Xid>
</Gid>
</ContactGid>
<FirstName/>
</Contact>
<ParentLocationGid>
<LocationGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>P099</Xid>
</Gid>
</LocationGid>
</ParentLocationGid>
</Location>
</GLogXMLElement>
<GLogXMLElement>
<ActualShipment>
<Shipment>
<ShipmentHeader>
<ShipmentGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>9979028275</Xid>
</Gid>
</ShipmentGid>
<TransactionCode>RC</TransactionCode>
<ShipmentRefnum>
<ShipmentRefnumQualifierGid>
<Gid>
<Xid>GLOG</Xid>
</Gid>
</ShipmentRefnumQualifierGid>
<ShipmentRefnumValue>P099.9979028275</ShipmentRefnumValue>
</ShipmentRefnum>
<ShipmentRefnum>
<ShipmentRefnumQualifierGid>
<Gid>
<Xid>PRIMARY TRACKING REF</Xid>
</Gid>
</ShipmentRefnumQualifierGid>
<ShipmentRefnumValue>9979028275</ShipmentRefnumValue>
</ShipmentRefnum>
<ShipmentRefnum>
<ShipmentRefnumQualifierGid>
<Gid>
<DomainName>PUBLIC</DomainName>
<Xid>REF</Xid>
</Gid>
</ShipmentRefnumQualifierGid>
<ShipmentRefnumValue>XXX</ShipmentRefnumValue>
</ShipmentRefnum>
<ServiceProviderGid>
<Gid>
<Xid>DHL EXPRESS</Xid>
</Gid>
</ServiceProviderGid>
<StartDate>20140902151500</StartDate>
<EndDate>20140903151500</EndDate>
<InvolvedParty>
<InvolvedPartyQualifierGid>
<Gid>
<DomainName>PUBLIC</DomainName>
<Xid>SHIPPER</Xid>
</Gid>
</InvolvedPartyQualifierGid>
<ContactRef>
<ContactGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>969000545</Xid>
</Gid>
</ContactGid>
</ContactRef>
</InvolvedParty>
<InvolvedParty>
<InvolvedPartyQualifierGid>
<Gid>
<DomainName>PUBLIC</DomainName>
<Xid>CONSIGNEE</Xid>
</Gid>
</InvolvedPartyQualifierGid>
<ContactRef>
<ContactGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>CONSIGNEE-9979028275</Xid>
</Gid>
</ContactGid>
</ContactRef>
</InvolvedParty>
<InvolvedParty>
<InvolvedPartyQualifierGid>
<Gid>
<DomainName>PUBLIC</DomainName>
<Xid>PAYEE</Xid>
</Gid>
</InvolvedPartyQualifierGid>
<ContactRef>
<ContactGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>PAYEE-969000545</Xid>
</Gid>
</ContactGid>
</ContactRef>
</InvolvedParty>
<Remark>
<RemarkSequence>114</RemarkSequence>
<RemarkQualifierGid>
<Gid>
<Xid>DESCRIPTION OF GOODS</Xid>
</Gid>
</RemarkQualifierGid>
<RemarkText>ECX</RemarkText>
</Remark>
<Remark>
<RemarkSequence>104</RemarkSequence>
<RemarkQualifierGid>
<Gid>
<Xid>TOTAL NUMBER OF PIECES</Xid>
</Gid>
</RemarkQualifierGid>
<RemarkText>1</RemarkText>
</Remark>
</ShipmentHeader>
<ShipmentHeader2>
<Perspective>B</Perspective>
<ShipmentTypeGid>
<Gid>
<Xid>TRANSPORT</Xid>
</Gid>
</ShipmentTypeGid>
<DimWeight>
<Weight>
<WeightValue>0.500</WeightValue>
<WeightUOMGid>
<Gid>
<Xid>KG</Xid>
</Gid>
</WeightUOMGid>
</Weight>
</DimWeight>
</ShipmentHeader2>
<SEquipment>
<SEquipmentGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>9979028275</Xid>
</Gid>
</SEquipmentGid>
</SEquipment>
<ShipmentStop>
<StopSequence>1</StopSequence>
<LocationRef>
<LocationGid>
<Gid>
<DomainName>PUBLIC</DomainName>
<Xid>LYS</Xid>
</Gid>
</LocationGid>
</LocationRef>
<IsPermanent>Y</IsPermanent>
<ShipmentStopDetail>
<Activity>P</Activity>
<ShipUnitGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>9979028275</Xid>
</Gid>
</ShipUnitGid>
</ShipmentStopDetail>
</ShipmentStop>
<ShipmentStop>
<StopSequence>99</StopSequence>
<LocationRef>
<LocationGid>
<Gid>
<DomainName>PUBLIC</DomainName>
<Xid>AMS</Xid>
</Gid>
</LocationGid>
</LocationRef>
<IsPermanent>Y</IsPermanent>
<ShipmentStopDetail>
<Activity>D</Activity>
<ShipUnitGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>9979028275</Xid>
</Gid>
</ShipUnitGid>
</ShipmentStopDetail>
</ShipmentStop>
<ShipUnit>
<ShipUnitGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>9979028275</Xid>
</Gid>
</ShipUnitGid>
<WeightVolume>
<Weight>
<WeightValue>1.700</WeightValue>
<WeightUOMGid>
<Gid>
<Xid>KG</Xid>
</Gid>
</WeightUOMGid>
</Weight>
<Volume>
<VolumeValue>0</VolumeValue>
<VolumeUOMGid>
<Gid>
<Xid>CUMTR</Xid>
</Gid>
</VolumeUOMGid>
</Volume>
</WeightVolume>
<UnitNetWeightVolume>
<Weight>
<WeightValue>0.00</WeightValue>
<WeightUOMGid>
<Gid>
<Xid>KG</Xid>
</Gid>
</WeightUOMGid>
</Weight>
<Volume>
<VolumeValue>0.00</VolumeValue>
<VolumeUOMGid>
<Gid>
<Xid>CUMTR</Xid>
</Gid>
</VolumeUOMGid>
</Volume>
</UnitNetWeightVolume>
<ShipUnitContent>
<LineNumber>1</LineNumber>
<ItemQuantity>
<WeightVolume>
<Weight>
<WeightValue>0</WeightValue>
<WeightUOMGid>
<Gid>
<Xid>KG</Xid>
</Gid>
</WeightUOMGid>
</Weight>
<Volume>
<VolumeValue>0</VolumeValue>
<VolumeUOMGid>
<Gid>
<Xid>CUMTR</Xid>
</Gid>
</VolumeUOMGid>
</Volume>
</WeightVolume>
</ItemQuantity>
<ShipUnitLineRefnum>
<ShipUnitLineRefnumQualifierGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>DNN</Xid>
</Gid>
</ShipUnitLineRefnumQualifierGid>
<ShipUnitLineRefnumValue>XXX</ShipUnitLineRefnumValue>
</ShipUnitLineRefnum>
</ShipUnitContent>
<Remark>
<RemarkSequence>103</RemarkSequence>
<RemarkQualifierGid>
<Gid>
<Xid>TOTAL BOOKED GROSS WEIGHT</Xid>
</Gid>
</RemarkQualifierGid>
<RemarkText>1.700 KG</RemarkText>
</Remark>
<Remark>
<RemarkSequence>104</RemarkSequence>
<RemarkQualifierGid>
<Gid>
<Xid>TOTAL NUMBER OF PIECES</Xid>
</Gid>
</RemarkQualifierGid>
<RemarkText>1</RemarkText>
</Remark>
<Remark>
<RemarkSequence>105</RemarkSequence>
<RemarkQualifierGid>
<Gid>
<Xid>CHARGEABLE WEIGHT</Xid>
</Gid>
</RemarkQualifierGid>
<RemarkText>0.500 KG</RemarkText>
</Remark>
<SEquipmentGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>9979028275</Xid>
</Gid>
</SEquipmentGid>
</ShipUnit>
</Shipment>
</ActualShipment>
</GLogXMLElement>
<GLogXMLElement>
<ShipmentStatus>
<ServiceProviderAlias>
<ServiceProviderAliasQualifierGid>
<Gid>
<Xid>GLOG</Xid>
</Gid>
</ServiceProviderAliasQualifierGid>
<ServiceProviderAliasValue>DHL EXPRESS</ServiceProviderAliasValue>
</ServiceProviderAlias>
<ShipmentRefnum>
<ShipmentRefnumQualifierGid>
<Gid>
<Xid>GLOG</Xid>
</Gid>
</ShipmentRefnumQualifierGid>
<ShipmentRefnumValue>P099.9979028275</ShipmentRefnumValue>
</ShipmentRefnum>
<StatusLevel>SHIPMENT</StatusLevel>
<StatusCodeGid>
<Gid>
<DomainName>P099</DomainName>
<Xid>SATA</Xid>
</Gid>
</StatusCodeGid>
<EventDate>20140903005200</EventDate>
<SSRemarks>EVENT_REMARK: <BR>SPECIFIC_REMARK: |||||||</SSRemarks>
<SSStop>
<SSLocation>
<LocationRefnumQualifierGid>
<Gid>
<Xid>GLOG</Xid>
</Gid>
</LocationRefnumQualifierGid>
<LocationID>BRU</LocationID>
</SSLocation>
</SSStop>
</ShipmentStatus>
</GLogXMLElement>
</TransmissionBody>
</Transmission>
-
10. Re: Re: Querying CLOB column (with XML content)
Jason_(A_Non) Sep 3, 2014 2:22 PM (in response to Veera_V)1 person found this helpfulTo refine what Odie provided, this appears to solve your issue, but it is not tested, besides verifying the XPath returns nodes. I did not confirm all the nodes you needed are returned so you will want to verify that
SELECT X.* from t , xmltable('/Transmission/TransmissionBody/GLogXMLElement/Location/*/Gid/DomainName' PASSING XMLTYPE(t.XML_BLOB) columns DomainName varchar2(15) path '.' ) X
You won't find any CLOB/String parsing routines here, given you are dealing with XML.
-
11. Re: Querying CLOB column (with XML content)
Veera_V Sep 4, 2014 1:14 AM (in response to Jason_(A_Non))If I dont know the full path (how should i change the query)
SELECT X.*
FROM t
, xmltable('/Transmission/*/DomainName'
PASSING XMLTYPE(t.XML_BLOB)
COLUMNS DOMAINNAME VARCHAR2(15) PATH './*'
) X
where t.no=317663815
for Odie sql, i m getting ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
-
12. Re: Re: Querying CLOB column (with XML content)
odie_63 Sep 4, 2014 7:57 AM (in response to Veera_V)for Odie sql, i m getting ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
Yes, my query was assuming only one occurrence of the target node.
Use a positional predicate to select only the first one :
SELECT X.* FROM t , xmltable('//DomainName[1]' PASSING xmlparse(document t.XML_BLOB) COLUMNS DOMAINNAME VARCHAR2(15) PATH '.' ) X where t.no=317663815
or
select xmlcast( xmlquery('//DomainName[1]' passing xmlparse(document t.xml_blob) returning content) as varchar2(15) ) from t where ...
-
13. Re: Re: Querying CLOB column (with XML content)
Veera_V Sep 4, 2014 8:51 AM (in response to odie_63)Your 1st sql returln all domainname (p099 and public - seems searches all node in one xml). I only need 1st domain name (and omit domains with PUBLIC)
- SELECT X.*
- FROM t
- , xmltable('//DomainName[1]'
- PASSING xmlparse(document t.XML_BLOB)
- COLUMNS DOMAINNAME VARCHAR2(15) PATH '.'
- ) X
- where t.no=317663815
-
14. Re: Querying CLOB column (with XML content)
padders Sep 4, 2014 9:05 AM (in response to Veera_V)Getting the first node from any path in a potentially unordered document seems a rather non-deterministic requirement.
The reason that '//DomainName[1]' doesn't work is that DomainName appears at multiple locations under Transmission/TransmissionBody, e.g.
GLogXMLElement/ActualShipment/Shipment/ShipmentStop/ShipmentStopDetail/ShipUnitGid/Gid/DomainName
GLogXMLElement/Location/Contact/ContactGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipUnit/SEquipmentGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/InvolvedParty/ContactRef/ContactGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/ShipmentGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipmentStop/LocationRef/LocationGid/Gid/DomainName
GLogXMLElement/Location/LocationGid/Gid/DomainName
GLogXMLElement/Location/ParentLocationGid/LocationGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/ShipmentRefnum/ShipmentRefnumQualifierGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipUnit/ShipUnitContent/ShipUnitLineRefnum/ShipUnitLineRefnumQualifierGid/Gid/DomainName
GLogXMLElement/ShipmentStatus/StatusCodeGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/InvolvedParty/InvolvedPartyQualifierGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/SEquipment/SEquipmentGid/Gid/DomainName
GLogXMLElement/ActualShipment/Shipment/ShipUnit/ShipUnitGid/Gid/DomainNameYou could try something like '(//DomainName[.!="PUBLIC"])[1]' but as I said before it seems non-deterministic.