Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to fetch nested xml node values

977256Aug 23 2016 — edited Aug 23 2016

Hi All, @"odie_63"

I have xml which has nested values at some places inside the xml.

I am using XML_TABLE function to get the values, but problem is when I use XML_TABLE then values are getting cross joined and total records are generated around 64.

But in actual records are present inside the XML are 12

Is there any way to get all the values without generating cross joins.?

RAW XML:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<Sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Source>

<SSCODE>SSD1</SSCODE>

<LOADID>94450001</LOADID>

<REC_PK>12345</REC_PK>

<Credential>

<PID>990000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5601</CID>

<Credential_STATUS>N</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CBIN>610096</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CBIN>334397</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CBIN>610098</CBIN>

</CV_Plans>

<PID_IDE>236547</PID_IDE>

</Credential>

<Credential>

<PID>880000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5820</CID>

<Credential_STATUS>Y</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CBIN>610096</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CBIN>610096</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CBIN>610096</CBIN>

</CV_Plans>

<PID_IDE>100009</PID_IDE>

</Credential>

</Source>

<Source>

<SSCODE>SSD1</SSCODE>

<LOADID>1234567</LOADID>

<REC_PK>98765</REC_PK>

<Credential>

<PID>880000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5601</CID>

<Credential_STATUS>N</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CBIN>610096</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CBIN>334397</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CBIN>610098</CBIN>

</CV_Plans>

<PID_IDE>1111112</PID_IDE>

</Credential>

<Credential>

<PID>990000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5820</CID>

<Credential_STATUS>Y</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CBIN>610097</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CBIN>610097</CBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CBIN>610097</CBIN>

</CV_Plans>

<PID_IDE>1303209</PID_IDE>

</Credential>

</Source>

</Sources>

Expected output:

Record.JPG

Query Used :

WITH data AS

(SELECT sn,

XMLParse(CONTENT xml\_data wellformed) xmlval

FROM

( SELECT sn,

      REPLACE(REPLACE(XML\_DATA,' xmlns:xsi="[http://www.w3.org/2001/XMLSchema-instance](http://www.w3.org/2001/XMLSchema-instance)"',NULL),'\<?xml version="1.0" encoding="UTF-8" standalone="no"?>',NULL) xml\_data

FROM

  (SELECT sn,'\<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<Sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Source>

<SSCODE>SSD1</SSCODE>

<LOADID>94450001</LOADID>

<REC_PK>12345</REC_PK>

<Credential>

<PID>990000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5601</CID>

<Credential_STATUS>N</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CCBIN>610096</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CCBIN>334397</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CCBIN>610098</CCBIN>

</CV_Plans>

<PID_IDE>236547</PID_IDE>

</Credential>

<Credential>

<PID>880000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5820</CID>

<Credential_STATUS>Y</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CCBIN>610096</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CCBIN>610096</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CCBIN>610096</CCBIN>

</CV_Plans>

<PID_IDE>100009</PID_IDE>

</Credential>

</Source>

<Source>

<SSCODE>SSD1</SSCODE>

<LOADID>1234567</LOADID>

<REC_PK>98765</REC_PK>

<Credential>

<PID>880000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5601</CID>

<Credential_STATUS>N</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CCBIN>610096</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CCBIN>334397</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CCBIN>610098</CCBIN>

</CV_Plans>

<PID_IDE>1111112</PID_IDE>

</Credential>

<Credential>

<PID>990000001</PID>

<IBT>MEDIPOINT</IBT>

<CID>S5820</CID>

<Credential_STATUS>Y</Credential_STATUS>

<CV_Plans>

<CV_TYPE>PRIMARY</CV_TYPE>

<CCBIN>610097</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>SECONDARY</CV_TYPE>

<CCBIN>610097</CCBIN>

</CV_Plans>

<CV_Plans>

<CV_TYPE>TERTIARY</CV_TYPE>

<CCBIN>610097</CCBIN>

</CV_Plans>

<PID_IDE>1303209</PID_IDE>

</Credential>

</Source>

</Sources>' XML_DATA FROM xml_ipl3 --where sn=6

  ) d

)

)

(SELECT DISTINCT

 tt.SSCODE

,tt.LOADID

,tt.REC\_PK

,t.PID

,t.IBT

,t.CID  

,t.Credential\_STATUS  

,a.CCBIN

,a.CV\_TYPE

FROM data d

,xmltable('/Sources/Source' PASSING (d.xmlval) COLUMNS 

SSCODE VARCHAR2(3000) PATH '//SSCODE' 

,LOADID VARCHAR2(3000) PATH '//LOADID' 

,REC\_PK VARCHAR2(3000) PATH '//REC\_PK' 

) tt 

,xmltable('/Sources/Source/Credential' PASSING (d.xmlval) COLUMNS 

PID           VARCHAR2(3000) PATH '//PID' 

,IBT VARCHAR2(3000) PATH '//IBT' 

,CID VARCHAR2(3000) PATH '//CID' 

,Credential\_STATUS VARCHAR2(3000) PATH '//Credential\_STATUS' 

) t        

,xmltable('/Sources/Source/Credential/CV\_Plans' PASSING (d.xmlval) COLUMNS 

CV\_TYPE           VARCHAR2(3000) PATH '//CV\_TYPE' 

,CCBIN           VARCHAR2(3000) PATH '//CCBIN'

) a

)

Query Output:

Capture.JPG
This post has been answered by odie_63 on Aug 23 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 20 2016
Added on Aug 23 2016
#performance-availability, #xml, #xml-db
3 comments
870 views