PL/SQL parsing Google XML

Google nicely provides xml with namespace nodes.

They are difficult to parse. Remember: You must include the namespace when using the .extract function with XMLType!

Here is an example though, of being able to parse the XML as it's returned:




SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<feed xmlns="http://www.w3.org/2005/Atom" xmlns:dxp="http://schemas.google.com/analytics/2009" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/">
<id>http://www.google.com/analytics/feeds/accounts/data@csuchico.edu</id>
<updated>2009-02-26T09:35:02.000-08:00</updated>
<title type="text">Profile list for data@csuchico.edu</title>
<link rel="self" type="application/atom+xml" href="http://www.google.com/analytics/feeds/accounts/default?alt=atom"/>
<author>
<name>Google Analytics</name>
</author>
<generator version="1.0">Google Analytics</generator>
<openSearch:totalResults>1</openSearch:totalResults>
<openSearch:startIndex>1</openSearch:startIndex>
<openSearch:itemsPerPage>1</openSearch:itemsPerPage>
<entry>
<id>http://www.google.com/analytics/feeds/accounts/ga:1631082</id>
<updated>2009-02-26T09:35:02.000-08:00</updated>
<title type="text">www.csuchico.edu</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics"/>
<dxp:property name="ga:accountId" value="942024"/>
<dxp:property name="ga:accountName" value="Chico State"/>
<dxp:property name="ga:profileId" value="1631082"/>
<dxp:property name="ga:webPropertyId" value="UA-942024-1"/>
<dxp:property name="ga:currency" value="USD"/>
<dxp:property name="ga:timezone" value="America/Los_Angeles"/>
<dxp:tableId>ga:1631082</dxp:tableId>
</entry>
</feed>'

).extract('/feed/entry/dxp:tableId', 'xmlns="http://www.w3.org/2005/Atom" xmlns:dxp="http://schemas.google.com/analytics/2009" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"').getStringVal() from dual;'