CODEWITHSUNDEEP
sqlxmloracle-database
user8235810
user8235810

Reputation:

Extract a substring from xml column from SQL query resultset

I have a table MYTABLE and column MYXML in Oracle 12 database. The column MYXML contains strings like below with varied lengths - 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:NodeData xmlns:ns2="http://abcd.org/xyz/schema/">
    <Head msgId="ACVF63980FGQW56123" orgId="12345" ts="2017-04-13T18:37:27+05:30" ver="1.0"/>
    <Info>
        <Identity id="45298" verifiedName="MFROMLA" type="TECH"/>
        <Rating name="HIGH"/>
    </Info>
    <Node Name="PQ" Desc="PreQualified" NodeID="2387ajdh231dqhhg21098"/>
</ns2:NodeData>

I want to extract just PQ from this column values using SQL query. How do I do that? I should be able to search using Name tag if possible and extracts its value.

Upvotes: 0

Views: 5636

Answers (3)

user8235810
user8235810

Reputation:

This worked for me - 

select name from mytable, xmltable(xmlnamespaces ('http://abcd.org/xyz/schema/' as "ns2"),'ns2:NodeData/Node' passing xmltype(myxml) columns name   varchar2(50) path '@Name');

Upvotes: 0

Aleksej
Aleksej

Reputation: 22959

If your column is a varchar2, you can use string methods; for example, these are two ways with and without regular expressions:

SELECT substr(myXml, 13, instr(myXml, '"', 1, 2) - 13),
       regexp_substr(myXml, '"([^"]+)"', 1, 1, 'i', 1)
FROM mytable

If your column is XMLTYPE, you can use the XML approach:

SELECT EXTRACT(myXml, '/Node/@Name')
from myTable

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5442

This is one query you could use, it extract attribute Name of node Node from your xml string. Assume that MYXML datatype is VARCHAR2

SELECT EXTRACT(xmltype(myxml), '/Node/@Name') 
FROM mytable;

Upvotes: 1

Related Questions