Select multiple detail attributes from xml parameter

[chris]

Hi -

I'm trying to parse the google analytics xml file returned from my
data query. Under the item node, there are two tags - dxp:dimension
and dxp:metric that I need to extract the value from. When I try my
query, I get "XQuery [value()]: 'value()' requires a singleton (or
empty sequence), found operand of type 'xdt:untypedAtomic *'". I need
to find a way around this but nothing yet. A minimal example follows.

Thanks.

chris

DECLARE @x xml
SET @x = '
<rss xmlns:atom="http://www.w3.org/2005/Atom" xmlns:openSearch="http://
a9.com/-/spec/opensearchrss/1.0/" version="2.0">
<channel>
<item>
<guid isPermaLink="false">http://www.google.com/analytics/feeds/
data?ids=xxx&amp;ga:pagePath=/some/page/path</guid>
<atom:updated>2009-12-15T16:00:00.001-08:00</atom:updated>
<title>ga:pagePath=/some/path/here</title>
<link>http://www.google.com/analytics</link>
<dxp:dimension xmlns:dxp="http://schemas.google.com/analytics/
2009" name="ga:pagePath" value="/path/is/interesting"/>
<dxp:metric xmlns:dxp="http://schemas.google.com/analytics/2009"
confidenceInterval="0.0" name="ga:pageviews" type="integer"
value="533"/>
</item>
</channel>
</rss>'

-- DOESN'T WORK
SELECT
ga.val.value('
declare namespace dxp="http://schemas.google.com/analytics/2009";
dxp:dimension[@value]', 'nvarchar(max)') AS [PagePath]
, ga.val.value('
declare namespace dxp="http://schemas.google.com/analytics/2009";
dxp:metric[@value]', 'nvarchar(max)') AS [Count]
FROM
@x.nodes('/rss/channel/item') AS ga(val)

-- WORKS but I need both values
-- SELECT
-- ga.val.value('@value', 'nvarchar(max)') AS [PagePath]
-- FROM
-- @x.nodes('declare namespace dxp="http://schemas.google.com/
analytics/2009"; /rss/channel/item/dxp:dimension') AS ga(val)

[chris]

On Dec 17, 9:52 am, chris <chris.cio...@gmail.com> wrote:
> Hi -
>
> I'm trying to parse the google analytics xml file returned from my
> data query.  Under the item node, there are two tags - dxp:dimension
> and dxp:metric that I need to extract the value from.  When I try my
> query, I get "XQuery [value()]: 'value()' requires a singleton (or
> empty sequence), found operand of type 'xdt:untypedAtomic *'".  I need
> to find a way around this but nothing yet.  A minimal example follows.
>

Reply to my own message. Thanks for Bob in the "parse xml" thread, I
got this to work:
SELECT
root.x.value('declare namespace dxp="http://schemas.google.com/
analytics/2009"; (dxp:dimension/@value, dxp:metric/@value)[1]',
'NVARCHAR(255)') AS a
, root.x.value('declare namespace dxp="http://schemas.google.com/
analytics/2009"; (dxp:dimension/@value, dxp:metric/@value)[2]',
'NVARCHAR(255)') AS b
FROM
@x.nodes('rss/channel/item') root(x)

Thanks Bob.

[Bob]

Another way to do that:

;WITH XMLNAMESPACES( 'http://schemas.google.com/analytics/2009' AS dxp )
SELECT
root.x.value('(dxp:dimension/@value)[1]','NVARCHAR(255)') AS a,
root.x.value('(dxp:metric/@value)[1]','NVARCHAR(255)') AS b

FROM @x.nodes('rss/channel/item') root(x)

I prefer WITH XMLNAMESPACES which saves you having to repeat the namespace
in queries like this. Also, not sure why you're using a sequence expression,
eg

(dxp:dimension/@value, dxp:metric/@value)[1]

This means, "if dimension/@value is not there, get metric/@value". Is that
what you wanted to do? I've simplified that bit in my query.

Start with this great article:
Introduction to XQuery in SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx

HTH
wBob

"chris" wrote:

> .
>