Thursday, June 2, 2011

PL/SQL XML parsing using XMLTABLE (Google API xml data)


SELECT * FROM
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.w3.org/2005/Atom',
                            'http://schemas.google.com/apps/2006' AS "apps"),
                                                       
'//entry/apps:property' PASSING XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<entry xmlns="http://www.w3.org/2005/Atom" xmlns:apps="http://schemas.google.com/apps/2006">
<id>api-url</id>
<updated>2011-06-02T18:08:01.969Z</updated>
<link rel="self" type="application/atom+xml" href="api-url"/>
<link rel="edit" type="application/atom+xml" href="api-url"/>
<apps:property name="enable" value="true"/>
<apps:property name="action" value="KEEP"/>
<apps:property name="forwardTo" value="someguy@yahoo.com"/>
</entry>')
COLUMNS
property VARCHAR2(4000) PATH '@name'
,val VARCHAR2(4000) PATH '@value'

) AS xmltbl

No comments:

Post a Comment