xml question

Question

Post reply

xml question

bluepaperbags

SSCommitted

Points: 1738
More actions
September 19, 2011 at 11:25 pm

#257408

I'm trying to parse this xml and generate another xml. I will like to produce a result (see below)
declare @xml xml
set @xml =
'<Datas xmlns="http://www.w3.org/2005/Atom" xmlns:dxp="http://schemas.google.com/analytics/2009" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/">
<Data SiteID="2" TestGoupID="1" GroupName="Production">
<id>http://www.google.com/analytics/feeds/data?ids=ga:20947043&dimensions=ga:day&metrics=ga:visitors&start-date=2011-09-14&end-date=2011-09-14</id>
<updated>2011-09-14T16:59:59.999-07:00</updated>
<title type="text">Google Analytics Data for Profile 20947043</title>
<link rel="self" type="application/atom+xml" href="https://www.google.com/analytics/feeds/data?max-results=100&end-date=2011-09-14&start-date=2011-09-14&metrics=ga%3Avisitors&ids=ga%3A20947043&dimensions=ga%3Aday" />
<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>100</openSearch:itemsPerPage>
<dxp:aggregates>
<dxp:metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="135276" />
</dxp:aggregates>
<dxp:containsSampledData>false</dxp:containsSampledData>
<dxp:dataSource>
<dxp:property name="ga:profileId" value="20947043" />
<dxp:property name="ga:webPropertyId" value="UA-10401800-1" />
<dxp:property name="ga:accountName" value="Corporation VKI" />
<dxp:tableId>ga:20947043</dxp:tableId>
<dxp:tableName>1.1 Primary Sites - Master</dxp:tableName>
</dxp:dataSource>
<dxp:endDate>2011-09-14</dxp:endDate>
<dxp:startDate>2011-09-14</dxp:startDate>
<entry>
<id>http://www.google.com/analytics/feeds/data?ids=ga:20947043&ga:day=14&start-date=2011-09-14&end-date=2011-09-14</id>
<updated>2011-09-13T17:00:00.001-07:00</updated>
<title type="text">ga:day=14</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics" />
<dxp:dimension name="ga:day" value="14" />
<dxp:metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="135276" />
</entry>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta" />
<Data SiteID="2" TestGoupID="3" GroupName="Control" />
</Datas>'
SELECT @xml.query('//Datas/Data')
Result:
<KPICounts>
<KPICount Name="ga:visitors" SiteID="2" TestGoupID="1" GroupName="Production" type="int" Value="135276" />
<KPICount Name ="ga:visitors" SiteID="2" TestGoupID="2" GroupName="Beta" type="int" Value="135276" />
<KPICount Name ="ga:visitors" SiteID="2" TestGoupID="3" GroupName="Control" type="int" Value="135276" />
</KPICounts>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

For some reason SQL Server does not accept the data as valid XML.

I get the error "XML parsing: line 3, character 75, semicolon expected"

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bluepaperbags SSCommitted Points: 1738 More actions · Answer 2

This is working fine but i'm missing 2 attributes. how can i get a result to be like this:

</KPICounts>

declare @xml xml

set @xml =

'<Datas>

<id>http://www.google.com/analytics/feeds/data?ids=ga:20947043&dimensions=ga:day&metrics=ga:visitors&start-date=2011-09-19&end-date=2011-09-19</id>

<title type="text">Google Analytics Data for Profile 20947043</title>

<name>Google Analytics</name>

</author>

<generator version="1.0">Google Analytics</generator>

<openSearch-totalResults>1</openSearch-totalResults>

<openSearch-startIndex>1</openSearch-startIndex>

<openSearch-itemsPerPage>100</openSearch-itemsPerPage>

<dxp-aggregates>

<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="100" />

<dxp-metric confidenceInterval="0.0" name="ga:visitors2" type="integer" value="1" />

</dxp-aggregates>

<dxp-containsSampledData>false</dxp-containsSampledData>

<dxp-dataSource>

<dxp-property name="ga:profileId" value="20947043" />

<dxp-property name="ga:webPropertyId" value="UA-10401800-1" />

<dxp-property name="ga:accountName" value="Corporation VKI" />

<dxp-tableId>ga:20947043</dxp-tableId>

<dxp-tableName>1.1 Primary Sites - Master</dxp-tableName>

</dxp-dataSource>

<dxp-endDate>2011-09-19</dxp-endDate>

<dxp-startDate>2011-09-19</dxp-startDate>

<entry>

<id>http://www.google.com/analytics/feeds/data?ids=ga:20947043&ga:day=19&start-date=2011-09-19&end-date=2011-09-19</id>

<dxp-dimension name="ga:day" value="19" />

<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="141322" />

</entry>

</Data>

<id>http://www.google.com/analytics/feeds/data?ids=ga:50279091&dimensions=ga:day&metrics=ga:visitors&start-date=2011-09-19&end-date=2011-09-19</id>

<title type="text">Google Analytics Data for Profile 50279091</title>

<name>Google Analytics</name>

</author>

<generator version="1.0">Google Analytics</generator>

<openSearch-totalResults>1</openSearch-totalResults>

<openSearch-startIndex>1</openSearch-startIndex>

<openSearch-itemsPerPage>100</openSearch-itemsPerPage>

<dxp-aggregates>

<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="0" />

</dxp-aggregates>

<dxp-containsSampledData>false</dxp-containsSampledData>

<dxp-dataSource>

<dxp-property name="ga:profileId" value="50279091" />

<dxp-property name="ga:webPropertyId" value="UA-10401800-1" />

<dxp-property name="ga:accountName" value="Corporation VKI" />

<dxp-tableId>ga:50279091</dxp-tableId>

<dxp-tableName>9.999 Beta Site - Master</dxp-tableName>

</dxp-dataSource>

<dxp-endDate>2011-09-19</dxp-endDate>

<dxp-startDate>2011-09-19</dxp-startDate>

<entry>

<id>http://www.google.com/analytics/feeds/data?ids=ga:50279091&ga:day=19&start-date=2011-09-19&end-date=2011-09-19</id>

<dxp-dimension name="ga:day" value="19" />

<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="0" />

</entry>

</Data>

<id>http://www.google.com/analytics/feeds/data?ids=ga:50279272&dimensions=ga:day&metrics=ga:visitors&start-date=2011-09-19&end-date=2011-09-19</id>

<title type="text">Google Analytics Data for Profile 50279272</title>

<name>Google Analytics</name>

</author>

<generator version="1.0">Google Analytics</generator>

<openSearch-totalResults>1</openSearch-totalResults>

<openSearch-startIndex>1</openSearch-startIndex>

<openSearch-itemsPerPage>100</openSearch-itemsPerPage>

<dxp-aggregates>

<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="0" />

<dxp-metric confidenceInterval="0.0" name="ga:visitors2" type="integer" value="10" />

</dxp-aggregates>

<dxp-containsSampledData>false</dxp-containsSampledData>

<dxp-dataSource>

<dxp-property name="ga:profileId" value="50279272" />

<dxp-property name="ga:webPropertyId" value="UA-10401800-1" />

<dxp-property name="ga:accountName" value="Corporation VKI" />

<dxp-tableId>ga:50279272</dxp-tableId>

<dxp-tableName>9.99 Control Site - Master</dxp-tableName>

</dxp-dataSource>

<dxp-endDate>2011-09-19</dxp-endDate>

<dxp-startDate>2011-09-19</dxp-startDate>

<entry>

<id>http://www.google.com/analytics/feeds/data?ids=ga:50279272&ga:day=19&start-date=2011-09-19&end-date=2011-09-19</id>

<dxp-dimension name="ga:day" value="19" />

<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="0" />

</entry>

</Data>

</Datas>'

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,@xml,'<Datas xmlns:def="http://www.w3.org/2005/Atom" xmlns:dxp="http://schemas.google.com/analytics/2009" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"/>'

SELECTName AS '@Name'

,SiteID AS '@SiteID'

,TestGoupID AS '@TestGoupID'

,GroupName AS '@GroupName'

,Type AS '@type'

,Value AS '@value'

FROM OPENXML(@idoc, '//Datas/Data')

WITH(SiteID varchar(50) '@SiteID',

TestGoupID varchar(50) '@TestGoupID',

GroupName varchar(50) '@GroupName',

Name varchar(50) 'dxp-aggregates/dxp-metric/@name',

Type varchar(50) 'dxp-aggregates/dxp-metric/@type',

Value varchar(50) 'dxp-aggregates/dxp-metric/@value'

) AS KPICount

FOR XML PATH('KPICount'), ROOT('KPICounts')

EXEC sp_xml_removedocument @idoc