Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 xml question

Author  Topic 

gongxia649
So Suave

344 Posts
Posted - 2011-09-19 : 14:58:48
Hi 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>

Ehan
Starting Member

19 Posts
Posted - 2011-09-20 : 06:51:15
Not sure with XQuery notation,

using OPEN XML/FOR XML ..

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/"/>'

SELECT Name AS '@Name',
SiteID AS '@SiteID'
,TestGoupID AS '@TestGoupID'
,GroupName AS '@GroupName'
,Type AS '@type'
,Value AS '@value'
FROM OPENXML(@idoc, '//def:Datas/def:Data',2)
WITH(SiteID varchar(50) '@SiteID',
TestGoupID varchar(50) '@TestGoupID',
GroupName varchar(50) '@GroupName',
Name varchar(50) 'def:entry/dxp:metric/@name',
Type varchar(50) 'def:entry/dxp:metric/@type',
Value varchar(50) 'def:entry/dxp:metric/@value'
) AS KPICount
FOR XML PATH('KPICount'), ROOT('KPICounts')

EXEC sp_xml_removedocument @idoc

Hope this helps.
Go to Top of Page

gongxia649
So Suave

344 Posts
Posted - 2011-09-21 : 20:51:58
This is working fine but i'm missing 2 attributes. how can i get a result to be like this:
<KPICounts>
<KPICount Name="ga:visitors" SiteID="2" TestGoupID="1" GroupName="Production" type="integer" value="100" />
<KPICount Name="ga:visitors2" SiteID="2" TestGoupID="1" GroupName="Production" type="integer" value="1" />
<KPICount Name="ga:visitors" SiteID="2" TestGoupID="2" GroupName="Beta" type="integer" value="0" />
<KPICount Name="ga:visitors" SiteID="2" TestGoupID="3" GroupName="Control" type="integer" value="0" />
<KPICount Name="ga:visitors2" SiteID="2" TestGoupID="3" GroupName="Control" type="integer" value="10" />
</KPICounts>




declare @xml xml
set @xml =
'<Datas>
<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-19&end-date=2011-09-19</id>
<updated>2011-09-19T16: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-19&start-date=2011-09-19&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="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>
<updated>2011-09-18T17:00:00.001-07:00</updated>
<title type="text">ga:day=19</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics" />
<dxp-dimension name="ga:day" value="19" />
<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="141322" />
</entry>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<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>
<updated>2011-09-19T16:59:59.999-07:00</updated>
<title type="text">Google Analytics Data for Profile 50279091</title>
<link rel="self" type="application/atom+xml" href="https://www.google.com/analytics/feeds/data?max-results=100&end-date=2011-09-19&start-date=2011-09-19&metrics=ga%3Avisitors&ids=ga%3A50279091&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="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>
<updated>2011-09-18T17:00:00.001-07:00</updated>
<title type="text">ga:day=19</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics" />
<dxp-dimension name="ga:day" value="19" />
<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="0" />
</entry>
</Data>
<Data SiteID="2" TestGoupID="3" GroupName="Control">
<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>
<updated>2011-09-19T16:59:59.999-07:00</updated>
<title type="text">Google Analytics Data for Profile 50279272</title>
<link rel="self" type="application/atom+xml" href="https://www.google.com/analytics/feeds/data?max-results=100&end-date=2011-09-19&start-date=2011-09-19&metrics=ga%3Avisitors&ids=ga%3A50279272&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="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>
<updated>2011-09-18T17:00:00.001-07:00</updated>
<title type="text">ga:day=19</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics" />
<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/"/>'

SELECT Name 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
Go to Top of Page

Ehan
Starting Member

19 Posts
Posted - 2011-09-22 : 07:57:25
If you have repeating element inside an element, you should start the pattern inside out.

SELECT Name AS '@Name'
,SiteID AS '@SiteID'
,TestGoupID AS '@TestGoupID'
,GroupName AS '@GroupName'
,Type AS '@type'
,Value AS '@value'
FROM OPENXML(@idoc, '//Datas/Data/dxp-aggregates/dxp-metric')
WITH( SiteID varchar(50) '../../@SiteID',
TestGoupID varchar(50) '../../@TestGoupID',
GroupName varchar(50) '../../@GroupName',
Name varchar(50) '@name',
Type varchar(50) '@type',
Value varchar(50) '@value'
) AS KPICount
FOR XML PATH('KPICount'), ROOT('KPICounts')

The problem with this is, if you donot have either <dxp-aggregates> or <dxp-metric> inside <Data>..it doesn't not parse even "SiteID" attribute in <Data>. Believe this is a limitation.

In this case, wouldn't matter if occurrence indicator for
<dxp-metric> is set to minOccurs="1" in the xml schema

http://msdn.microsoft.com/en-us/library/ms186918(v=SQL.90).aspx
Go to Top of Page

gongxia649
So Suave

344 Posts
Posted - 2011-09-22 : 12:27:54
Thanks, but im still getting an error.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =
'<Datas>
<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-19&end-date=2011-09-19</id>
<updated>2011-09-19T16: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-19&start-date=2011-09-19&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="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>
<updated>2011-09-18T17:00:00.001-07:00</updated>
<title type="text">ga:day=19</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics" />
<dxp-dimension name="ga:day" value="19" />
<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="141322" />
</entry>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<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>
<updated>2011-09-19T16:59:59.999-07:00</updated>
<title type="text">Google Analytics Data for Profile 50279091</title>
<link rel="self" type="application/atom+xml" href="https://www.google.com/analytics/feeds/data?max-results=100&end-date=2011-09-19&start-date=2011-09-19&metrics=ga%3Avisitors&ids=ga%3A50279091&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="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>
<updated>2011-09-18T17:00:00.001-07:00</updated>
<title type="text">ga:day=19</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics" />
<dxp-dimension name="ga:day" value="19" />
<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="0" />
</entry>
</Data>
<Data SiteID="2" TestGoupID="3" GroupName="Control">
<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>
<updated>2011-09-19T16:59:59.999-07:00</updated>
<title type="text">Google Analytics Data for Profile 50279272</title>
<link rel="self" type="application/atom+xml" href="https://www.google.com/analytics/feeds/data?max-results=100&end-date=2011-09-19&start-date=2011-09-19&metrics=ga%3Avisitors&ids=ga%3A50279272&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="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>
<updated>2011-09-18T17:00:00.001-07:00</updated>
<title type="text">ga:day=19</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics" />
<dxp-dimension name="ga:day" value="19" />
<dxp-metric confidenceInterval="0.0" name="ga:visitors" type="integer" value="0" />
</entry>
</Data>
</Datas>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT Name AS '@Name'
,SiteID AS '@SiteID'
,TestGoupID AS '@TestGoupID'
,GroupName AS '@GroupName'
,Type AS '@type'
,Value AS '@value'
FROM OPENXML(@idoc, '//Datas/Data/dxp-aggregates/dxp-metric')
WITH( Name varchar(50) '@name',
SiteID varchar(50) '../../@SiteID',
TestGoupID varchar(50) '../../@TestGoupID',
GroupName varchar(50) '../../@GroupName',
Type varchar(50) '@type',
Value varchar(50) '@value'
) AS KPICount
FOR XML PATH('KPICount'), ROOT('KPICounts')
Go to Top of Page

Ehan
Starting Member

19 Posts
Posted - 2011-09-22 : 12:34:04
Replace all & with &

increase the length of @doc or change the datatype to xml
Go to Top of Page

Ehan
Starting Member

19 Posts
Posted - 2011-09-22 : 12:36:45
ignore & replace comment, you might already have it as &
Go to Top of Page

Ehan
Starting Member

19 Posts
Posted - 2011-09-22 : 12:40:31
oops.. i meant &amp;
Go to Top of Page

gongxia649
So Suave

344 Posts
Posted - 2011-09-22 : 14:10:02
what do you mean. sorry i dont get it.
Go to Top of Page

Ehan
Starting Member

19 Posts
Posted - 2011-09-22 : 14:28:51
you are getting an error as your @doc varchar(1000) is not long enouch to hold the entire xml
try changing the datatype to xml
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site