Hi, I've got an XML file where I want to parse out the fields into a DB table:
<?xml version="1.0" encoding="UTF-8"?>
<FIXML><Batch><MktDataFull BizDt="2012-07-13"><Instrmt Sym="JCPRXU" ID="JCPRXU" Desc="JCP.SR.XR.USD" SecTyp="CDS" Src="H" SubTyp="S" MMY="201209" MatDt="2012-09-20" Mult="0.01" Exch="CMD" UOM="Ccy" UOMCcy="USD" UOMQty="1" PxUOM="IPNT" ValMeth="CDS" CpnRt="1.0" IntAcrl="2012-06-20" CpnPmt="2012-09-20" NotnlPctOut="100.0" Snrty="SR" RstrctTyp="XR" DayCntMeth="ACT/360" Tenor="0M"><AID AltID="US708130AC31" AltIDSrc="105"/><AID AltID="JCP.SR.XR.USD.12U.100" AltIDSrc="101"/><AID AltID="JCPRXU 201209 1" AltIDSrc="H"/><AID AltID="JCPRXU 201209 1" AltIDSrc="100"/><Evnt EventTyp="5" Dt="2008-09-19"/><Evnt EventTyp="7" Dt="2012-09-19"/><Evnt EventTyp="19" Dt="2012-10-05"/><Evnt EventTyp="100" Dt="2012-07-16"/><Evnt EventTyp="8" Dt="2012-07-14"/><Evnt EventTyp="9" Dt="2012-09-20"/><Evnt EventTyp="101" Dt="2012-03-20"/><Evnt EventTyp="102" Dt="2008-09-20"/><Evnt EventTyp="103" Dt="2008-09-22"/><Evnt EventTyp="104" Dt="2012-09-19"/><Evnt EventTyp="111" Dt="2012-09-20"/><Evnt EventTyp="112" Dt="2012-06-20"/><Evnt EventTyp="113" Dt="2012-03-20"/><Evnt EventTyp="114" Dt="2012-07-12"/><Evnt EventTyp="115" Dt="2012-07-16"/></Instrmt><Full Typ="6" Px="99.7433368" Mkt="CMD" QCond="6" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="6" Px="234.5254" Mkt="CMD" QCond="6" PxTyp="6" OpenClsSettlFlag="1"></Full><Full Typ="Y" Px="40.0" Mkt="CMD" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="6" Px="234.5212" Mkt="CMD" QCond="7" PxTyp="6" OpenClsSettlFlag="1"></Full><Full Typ="B" Mkt="CMD" OpenClsSettlFlag="4" Sz="0"></Full><Full Typ="C" Mkt="CMD" OpenClsSettlFlag="4" Sz="0"></Full><Full Typ="z" Px="0.18" Mkt="CMD" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="y" Px="0.1899965" Mkt="CMD" QCond="6" PxTyp="5" OpenClsSettlFlag="1"></Full><InstrmtExt><Attrb Typ="100" Val="24"/><Attrb Typ="101" Val="0"/><Attrb Typ="109" Val="0"/><Attrb Typ="103" Val="24"/><Attrb Typ="102" Val="24"/><Attrb Typ="110" Val="3"/><Attrb Typ="29" Val="Y"/><Attrb Typ="112" Val="Y"/></InstrmtExt></MktDataFull></Batch></FIXML>
Right now, I'm only trying to extract the first 3 fields, BizDt, Sym and ID. I'm using the following to parse:
SELECT
EXTRACT(value(p), '/BizDt').getStringVal() AS DATE_,
EXTRACT(value(p), '/Instrmt/Sym').getStringVal() AS SYM,
EXTRACT(value(p), '/Instrmt/ID').getStringVal() AS ID_
FROM TABLE_NAME s,
TABLE(XMLSEQUENCE(EXTRACT(xmlType.createXml(s.CDS_CLOB), 'FIXML/Batch/MktDataFull/*'))) p
WHERE s.ID_ = 1
But I'm not getting anything back. My guess is because the XML data does not have formal open and close tags, because if I change my XML to this:
<?xml version="1.0" encoding="UTF-8"?>
<FIXML><Batch><MktDataFull> <BizDt>2012-07-13</BizDt> <Instrmt> <Sym>JCPRXU</Sym> <ID>JCPRXU</ID> <Desc>JCP.SR.XR.USD</Desc> <SecTyp>CDS</SecTyp> </Instrmt> </MktDataFull></Batch></FIXML>
I was able to get the data. So in order so solve this issue, what should I do with my original XML ? Should I format the tags?
Thank you,