I need to parse the following XML:
<?xml version="1.0" encoding="Windows-1251"?>
<Dialog>
<Item QID="9" Answer="1000" UniqueGrInd="1"/>
<Item QID="10" Answer="1001 UniqueGrInd="2"/>
</Dialog>
And return a result like this:
QID Answer UniqueGrInd
9 1000 1
10 1001 2
You want to use XMLTABLE, like so:
WITH sample_data AS (SELECT 1 id, XMLTYPE('<?xml version="1.0" encoding="Windows-1251"?>
<Dialog>
<Item QID="9" Answer="1000" UniqueGrInd="1"/>
<Item QID="10" Answer="1001" UniqueGrInd="2"/>
</Dialog>') xmldata FROM dual UNION ALL
SELECT 2 id, XMLTYPE('<?xml version="1.0" encoding="Windows-1251"?>
<Dialog>
<Item QID="12" Answer="2000" UniqueGrInd="1"/>
<Item QID="13" Answer="2001" UniqueGrInd="2"/>
<Item QID="14" Answer="2002" UniqueGrInd="3"/>
</Dialog>') xmldata FROM dual)
SELECT sd.id,
x.qid,
x.answer,
x.unique_gr_ind
FROM sample_data sd
CROSS JOIN XMLTABLE('Dialog/Item' PASSING sd.xmldata
COLUMNS qid INTEGER PATH '@QID',
answer INTEGER PATH '@Answer',
unique_gr_ind INTEGER PATH '@UniqueGrInd') x;
ID QID ANSWER UNIQUE_GR_IND
---- --- ------ -------------
1 9 1000 1
1 10 1001 2
2 12 2000 1
2 13 2001 2
2 14 2002 3
