CODEWITHSUNDEEP
xmloracle-databaseextractclob
HCC
HCC

Reputation: 33

Oracle 10g: Reading XML values from Clob using XMLSequence and ExtractValue

I'm starting to feel a bit dim. I've read a lot of web pages and tried a number of methods to do something that seems relatively simple.

I've some XML stored a table. The table contains an ID and the XML in a CLOB. Something like:

ID = 1

<?xml version="1.0" encoding="UTF-8" ?>
<CricketGame xmlns="http://www.somewhere.com/cricket/2002/09" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="2002A" xsi:type="CricketGame" >
<TeamInfo TeamId="WestCountry" />
<SessionInfo SessionId="XhRya5m999988888" RestartSession="false" />
<Errors>
<Error ErrorText="Generic Error" ErrorCode="700" SupplierErrorText="Connection: DECLINED" />
<Error ErrorText="Generic Error" ErrorCode="701" SupplierErrorText="Account Error" />
</Errors>
</CricketGame>

I have been trying to use a combination of extract and XMLSequence to create a Table and then extractvalue to get the specific values from ErrorText, ErrorCode and SupplierErrorText. My SQL:

SELECT 
extractvalue(value(p), '/Error/@ErrorText') as errText,
extractvalue(value(p), '/Error/@ErrorCode') as errCode,
extractvalue(value(p), '/Error/@SupplierErrorText') as supErrText
FROM gamestable  s, 
  Table(
    XMLSequence(
    extract(xmltype(s.xml), '/CricketGame/Errors/Error')
    )
  ) p
where 
s.gameID = 1

The SQL doesn't return any errors, but it doesn't return the data either.

Can anyone see what I'm doing wrong? Am I going about this totally wrong? Is it perhaps something to do with the xmlns?

Many thanks in advance!

Upvotes: 3

Views: 12848

Answers (1)

Jon Heller
Jon Heller

Reputation: 36922

You're right, the problem is related to xmlns. I don't understand XML well, so I can't tell you if this is a good solution, but it at least appears to work for this example:

SELECT 
extractvalue(value(p), '/Error/@ErrorText', 'xmlns="http://www.somewhere.com/cricket/2002/09"') as errText,
extractvalue(value(p), '/Error/@ErrorCode', 'xmlns="http://www.somewhere.com/cricket/2002/09"') as errCode,
extractvalue(value(p), '/Error/@SupplierErrorText', 'xmlns="http://www.somewhere.com/cricket/2002/09"') as supErrText
FROM gamestable  s, 
  Table(
    XMLSequence(
    extract(xmltype(s.xml), '/CricketGame/Errors/Error', 'xmlns="http://www.somewhere.com/cricket/2002/09"')
    )
  ) p
where 
s.gameID = 1

Upvotes: 2

Related Questions