Google Analytics XML file to SQL2005 table

Question

Google Analytics XML file to SQL2005 table

mfink

SSCommitted

Points: 1632
More actions
July 20, 2011 at 8:54 am

#241110

I am new to XML filesand know nothing about them. I would like to load the xml file that comes out of google analytics to SQL server 2005 table. This information will be combine with other data.
I would like the counts for the regions. This xml file will be email weekly to us. Any assistance will be greatly appreciated.
<?xml version="1.0" ?>
- <AnalyticsReport>
- <Report name="Custom Report">
- <Title id="Title">
<CompareStateHelp>Compare report data to overall site performance and include report data when calculating site performance (% of Site Total or Site Avg.).</CompareStateHelp>
<ShowDateControl>true</ShowDateControl>
<PrimaryDateRange>July 12, 2011 - July 18, 2011</PrimaryDateRange>
<Compare>false</Compare>
<Sampled>false</Sampled>
<CompareStateMsg>Site</CompareStateMsg>
<Name>Custom - Daily by State</Name>
<CompareMsg>Comparing to:</CompareMsg>
<ProfileName>www.bigfiretruckwebpage.com</ProfileName>
<FilterApplied>false</FilterApplied>
<ShowCompare>true</ShowCompare>
<DetailSeparator>|</DetailSeparator>
</Title>
- <Graph id="Graph">
<ShowHover>true</ShowHover>
<Format>NORMAL</Format>
<XAxisTitle>Day</XAxisTitle>
<Compare>false</Compare>
<XAxisLabel>Jul 18</XAxisLabel>
<SelectedSerie>primary</SelectedSerie>
<SelectedSerie>compare</SelectedSerie>
- <Serie>
<SelectionStartIndex>0</SelectionStartIndex>
<SelectionEndIndex>6</SelectionEndIndex>
- <Style>
<Name>primary-blue</Name>
<PointShape>CIRCLE</PointShape>
<PointRadius>9</PointRadius>
<FillColor>30668</FillColor>
<FillAlpha>10</FillAlpha>
<LineThickness>4</LineThickness>
<ActiveColor>30668</ActiveColor>
<InactiveColor>11654895</InactiveColor>
</Style>
<Id>primary</Id>
<Label>Visitors</Label>
<YLabel>0</YLabel>
<YLabel>5</YLabel>
<YLabel>10</YLabel>
<ValueCategory>visitors</ValueCategory>
- <Point>
<Value>4</Value>
<Label>Tuesday, July 12, 2011</Label>
</Point>
- <Point>
<Value>4</Value>
<Label>Wednesday, July 13, 2011</Label>
</Point>
- <Point>
<Value>5</Value>
<Label>Thursday, July 14, 2011</Label>
</Point>
- <Point>
<Value>2</Value>
<Label>Friday, July 15, 2011</Label>
</Point>
- <Point>
<Value>0</Value>
<Label>Saturday, July 16, 2011</Label>
</Point>
- <Point>
<Value>2</Value>
<Label>Sunday, July 17, 2011</Label>
</Point>
- <Point>
<Value>10</Value>
<Label>Monday, July 18, 2011</Label>
</Point>
</Serie>
</Graph>
- <Narrative id="Narrative">
- <Line>
<LineType>NORMAL</LineType>
<Content>This custom dimension resulted in 26 Visitors via 15 days + regions</Content>
</Line>
<Type>NORMAL</Type>
<Compare>false</Compare>
</Narrative>
- <Scorecard id="Scorecard">
- <Group>
- <Item>
<Name>Visitors</Name>
<HelpMsg>The total number of visitors to your site over the given time range.</HelpMsg>
<Active>true</Active>
- <Line>
<Value>26</Value>
<Label>% of Site Total:</Label>
<DisplayValue>false</DisplayValue>
</Line>
- <Line>
<Value>26</Value>
<Label>% of Site Total:</Label>
<PercentChange>100.00%</PercentChange>
<DisplayValue>false</DisplayValue>
</Line>
<InverseCompare>false</InverseCompare>
<Segments>false</Segments>
<Goal>false</Goal>
- <Sparkline>
<PrimaryValue>4.0</PrimaryValue>
<PrimaryValue>4.0</PrimaryValue>
<PrimaryValue>5.0</PrimaryValue>
<PrimaryValue>2.0</PrimaryValue>
<PrimaryValue>0.0</PrimaryValue>
<PrimaryValue>2.0</PrimaryValue>
<PrimaryValue>10.0</PrimaryValue>
<SparklineValueType>(returning_visitors+new_visitors)</SparklineValueType>
<ControlForGraphMetric>true</ControlForGraphMetric>
<Compare>false</Compare>
</Sparkline>
</Item>
- <Item>
<Name>Visits</Name>
<HelpMsg>The number of visits to your site.</HelpMsg>
<Active>true</Active>
- <Line>
<Value>30</Value>
<Label>% of Site Total:</Label>
<DisplayValue>false</DisplayValue>
</Line>
- <Line>
<Value>30</Value>
<Label>% of Site Total:</Label>
<PercentChange>100.00%</PercentChange>
<DisplayValue>false</DisplayValue>
</Line>
<InverseCompare>false</InverseCompare>
<Segments>false</Segments>
<Goal>false</Goal>
- <Sparkline>
<PrimaryValue>5.0</PrimaryValue>
<PrimaryValue>5.0</PrimaryValue>
<PrimaryValue>5.0</PrimaryValue>
<PrimaryValue>3.0</PrimaryValue>
<PrimaryValue>0.0</PrimaryValue>
<PrimaryValue>2.0</PrimaryValue>
<PrimaryValue>10.0</PrimaryValue>
<SparklineValueType>visits</SparklineValueType>
<ControlForGraphMetric>true</ControlForGraphMetric>
<Compare>false</Compare>
</Sparkline>
</Item>
<Name>New tab</Name>
<Active>true</Active>
</Group>
<Format>COMPACT</Format>
<Compare>true</Compare>
</Scorecard>
- <TabView id="TabView">
<ViewSelected>0</ViewSelected>
- <LastTab>
<GroupIndex>0</GroupIndex>
<TabName>New tab</TabName>
</LastTab>
<MaxNumberOfTabsShown>1</MaxNumberOfTabsShown>
<PivotViewEnabled>true</PivotViewEnabled>
<Compare>false</Compare>
<TabSelected>0</TabSelected>
<SegmentLocked>true</SegmentLocked>
<TableViewsEnabled>true</TableViewsEnabled>
<TrendingView>false</TrendingView>
</TabView>
- <Table id="Table">
- <Column>
<Name>Visitors</Name>
<Active>true</Active>
<ScaleFactor>1</ScaleFactor>
<RawName>visitors</RawName>
<Total>26</Total>
</Column>
- <Column>
<Name>Visits</Name>
<Active>true</Active>
<ScaleFactor>1</ScaleFactor>
<RawName>visits</RawName>
<Total>30</Total>
</Column>
<ColumnShow>0</ColumnShow>
<ZippyEnabled>false</ZippyEnabled>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>4</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>4</Value>
</Content>
</Cell>
<RowLabel>1</RowLabel>
<DrilldownId>0006</DrilldownId>
<DrilldownId>Virginia</DrilldownId>
<PrimaryKey>Monday, July 18, 2011</PrimaryKey>
<Key>Monday, July 18, 2011</Key>
<Key>Virginia</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>3</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>3</Value>
</Content>
</Cell>
<RowLabel>2</RowLabel>
<DrilldownId>0000</DrilldownId>
<DrilldownId>Pennsylvania</DrilldownId>
<PrimaryKey>Tuesday, July 12, 2011</PrimaryKey>
<Key>Tuesday, July 12, 2011</Key>
<Key>Pennsylvania</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>3</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>3</Value>
</Content>
</Cell>
<RowLabel>3</RowLabel>
<DrilldownId>0002</DrilldownId>
<DrilldownId>Virginia</DrilldownId>
<PrimaryKey>Thursday, July 14, 2011</PrimaryKey>
<Key>Thursday, July 14, 2011</Key>
<Key>Virginia</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>3</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>3</Value>
</Content>
</Cell>
<RowLabel>4</RowLabel>
<DrilldownId>0006</DrilldownId>
<DrilldownId>Wisconsin</DrilldownId>
<PrimaryKey>Monday, July 18, 2011</PrimaryKey>
<Key>Monday, July 18, 2011</Key>
<Key>Wisconsin</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>2</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>4</Value>
</Content>
</Cell>
<RowLabel>5</RowLabel>
<DrilldownId>0001</DrilldownId>
<DrilldownId>Washington</DrilldownId>
<PrimaryKey>Wednesday, July 13, 2011</PrimaryKey>
<Key>Wednesday, July 13, 2011</Key>
<Key>Washington</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>2</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>2</Value>
</Content>
</Cell>
<RowLabel>6</RowLabel>
<DrilldownId>0002</DrilldownId>
<DrilldownId>Washington</DrilldownId>
<PrimaryKey>Thursday, July 14, 2011</PrimaryKey>
<Key>Thursday, July 14, 2011</Key>
<Key>Washington</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>2</Value>
</Content>
</Cell>
<RowLabel>7</RowLabel>
<DrilldownId>0000</DrilldownId>
<DrilldownId>Washington</DrilldownId>
<PrimaryKey>Tuesday, July 12, 2011</PrimaryKey>
<Key>Tuesday, July 12, 2011</Key>
<Key>Washington</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
<RowLabel>8</RowLabel>
<DrilldownId>0001</DrilldownId>
<DrilldownId>Illinois</DrilldownId>
<PrimaryKey>Wednesday, July 13, 2011</PrimaryKey>
<Key>Wednesday, July 13, 2011</Key>
<Key>Illinois</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>2</Value>
</Content>
</Cell>
<RowLabel>9</RowLabel>
<DrilldownId>0003</DrilldownId>
<DrilldownId>Virginia</DrilldownId>
<PrimaryKey>Friday, July 15, 2011</PrimaryKey>
<Key>Friday, July 15, 2011</Key>
<Key>Virginia</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
<RowLabel>10</RowLabel>
<DrilldownId>0003</DrilldownId>
<DrilldownId>Wisconsin</DrilldownId>
<PrimaryKey>Friday, July 15, 2011</PrimaryKey>
<Key>Friday, July 15, 2011</Key>
<Key>Wisconsin</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
<RowLabel>11</RowLabel>
<DrilldownId>0005</DrilldownId>
<DrilldownId>District of Columbia</DrilldownId>
<PrimaryKey>Sunday, July 17, 2011</PrimaryKey>
<Key>Sunday, July 17, 2011</Key>
<Key>District of Columbia</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
<RowLabel>12</RowLabel>
<DrilldownId>0005</DrilldownId>
<DrilldownId>Virginia</DrilldownId>
<PrimaryKey>Sunday, July 17, 2011</PrimaryKey>
<Key>Sunday, July 17, 2011</Key>
<Key>Virginia</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
<RowLabel>13</RowLabel>
<DrilldownId>0006</DrilldownId>
<DrilldownId>Maryland</DrilldownId>
<PrimaryKey>Monday, July 18, 2011</PrimaryKey>
<Key>Monday, July 18, 2011</Key>
<Key>Maryland</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
<RowLabel>14</RowLabel>
<DrilldownId>0006</DrilldownId>
<DrilldownId>Florida</DrilldownId>
<PrimaryKey>Monday, July 18, 2011</PrimaryKey>
<Key>Monday, July 18, 2011</Key>
<Key>Florida</Key>
</Row>
- <Row>
<Drilldown>true</Drilldown>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
- <Cell>
<Active>true</Active>
<InverseCompare>false</InverseCompare>
- <Content>
<Value>1</Value>
</Content>
</Cell>
<RowLabel>15</RowLabel>
<DrilldownId>0006</DrilldownId>
<DrilldownId>Washington</DrilldownId>
<PrimaryKey>Monday, July 18, 2011</PrimaryKey>
<Key>Monday, July 18, 2011</Key>
<Key>Washington</Key>
</Row>
<IncludeLinker>false</IncludeLinker>
<DataSetLabel>July 12, 2011 - July 18, 2011</DataSetLabel>
<Compare>false</Compare>
<Sampled>false</Sampled>
<RowDataRange>1 - 15 of 15</RowDataRange>
<RowEnd>15</RowEnd>
<FilterString />
<ColumnStart>0</ColumnStart>
<AdvancedFilterShown>false</AdvancedFilterShown>
- <ActiveColumn>
<Name>Visitors</Name>
<Active>true</Active>
<ScaleFactor>1</ScaleFactor>
<RawName>visitors</RawName>
<Total>26</Total>
</ActiveColumn>
- <ActiveColumn>
<Name>Visits</Name>
<Active>true</Active>
<ScaleFactor>1</ScaleFactor>
<RawName>visits</RawName>
<Total>30</Total>
</ActiveColumn>
<SeeAllLabel>see all</SeeAllLabel>
<DeltaMessage>% Change</DeltaMessage>
<SimultaneousDrilldown>false</SimultaneousDrilldown>
- <KeyColumn>
<DrilldownId>nth_day</DrilldownId>
<KeyName>nth_day</KeyName>
</KeyColumn>
- <KeyColumn>
<DrilldownId>region</DrilldownId>
<KeyName>region</KeyName>
</KeyColumn>
<LinkParams>cid=1</LinkParams>
<RowShow>500</RowShow>
<RowCount>15</RowCount>
<TableFilteringEnabled>true</TableFilteringEnabled>
<KeyFilterEnabled>true</KeyFilterEnabled>
<RowStart>0</RowStart>
<HasCustomTooltips>false</HasCustomTooltips>
<ColumnCount>0</ColumnCount>
<NoDataLabel>There is no data for this view.</NoDataLabel>
<FilterType>0</FilterType>
<ColumnEnd>0</ColumnEnd>
<AdvancedFilterApplied>false</AdvancedFilterApplied>
</Table>
- <TableFilter id="TableFilter">
- <Group>
- <Column>
<Name>Visitors</Name>
<RawName>visitors</RawName>
</Column>
- <Column>
<Name>Visits</Name>
<RawName>visits</RawName>
</Column>
<GroupName>New tab</GroupName>
</Group>
<AdvSegmentApplied>false</AdvSegmentApplied>
<TableView>0</TableView>
<Compare>false</Compare>
- <KeyColumnHeaders>
- <entry>
- <key>
<KeyColumnHeader>nth_day</KeyColumnHeader>
</key>
- <value>
<KeyColumnHeader>Day</KeyColumnHeader>
</value>
</entry>
- <entry>
- <key>
<KeyColumnHeader>region</KeyColumnHeader>
</key>
- <value>
<KeyColumnHeader>Region</KeyColumnHeader>
</value>
</entry>
</KeyColumnHeaders>
</TableFilter>
</Report>
</AnalyticsReport>

Viewing 15 posts - 1 through 15 (of 18 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

It helps if you provide expected output based on the data provided. As it is, I had to guess at what you were looking for.

The following code should at least give a starting point.

SELECT c.value('(PrimaryKey)[1]', 'varchar(30)') AS Dte

, c.value('(DrilldownId)[2]', 'varchar(25)') AS Region

, c.value('(Cell/Content/Value)[1]', 'int') AS Cnt

FROM @x.nodes('/AnalyticsReport/Report/Table/Row') AS T(c)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mfink SSCommitted Points: 1632 More actions · Answer 2

Drew

SO I should load this into a xml type column and run this select against it?

Mark

drew.allen SSC Guru Points: 76985 More actions · Answer 3

I used an XML variable. If you use an XML column, you'll probably need to use CROSS APPLY in order to evaluate for each row.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mfink SSCommitted Points: 1632 More actions · Answer 4

Drew

I really don't know anything about XML can you please be more specific. Yes please give me the exact code you used

thank you

drew.allen SSC Guru Points: 76985 More actions · Answer 5

My previous post contains all the information specific to XML. The rest is standard SQL stuff. You declare an XML variable and set the value of the variable to your XML document as a string. Below is an abbreviated example.

DECLARE @x xml

SET @x='<xmldocument>.....</xmldocument>'

That's the extent of my code.

You will need to worry about how to get the information out of the file and into either an xml variable or column. OPENROWSET may be the best option, but I can't help you with that, because it's locked down here. SSIS is also an option.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mfink SSCommitted Points: 1632 More actions · Answer 6

Msg 9438, Level 16, State 1, Line 2

XML parsing: line 2, character 6, text/xmldecl not at the beginning of input

drew.allen SSC Guru Points: 76985 More actions · Answer 7

mfink (7/25/2011)
Msg 9438, Level 16, State 1, Line 2
XML parsing: line 2, character 6, text/xmldecl not at the beginning of input

You can't have any whitespace before the <?xml version="1.0" ?>.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mfink SSCommitted Points: 1632 More actions · Answer 8

mfink

SSCommitted

Points: 1632

July 25, 2011 at 12:27 pm

#1358055

Yes I did. ANd that solved the problem

:w00t:

mfink SSCommitted Points: 1632 More actions · Answer 9

mfink

SSCommitted

Points: 1632

July 25, 2011 at 12:28 pm

#1358058

Thank you so much

mfink SSCommitted Points: 1632 More actions · Answer 10

Drew

How would I add the profileName to that select?

Mark

drew.allen SSC Guru Points: 76985 More actions · Answer 11

mfink (7/25/2011)
How would I add the profileName to that select?

See if you can figure it out for yourself. You just need to determine the relative path from your current node to your ProfileName.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mfink SSCommitted Points: 1632 More actions · Answer 12

AnalyticsReport('(ProfileName)[0]','varchar(100)') as WebPage1,

c.profileName('(ProfileName)[0]','varchar(100)') as WebPage1,

c.value('(ProfileName)[0]','varchar(100)') as WebPage2,

none of these am I headed in the right direction?

drew.allen SSC Guru Points: 76985 More actions · Answer 13

mfink (7/25/2011)
AnalyticsReport('(ProfileName)[0]','varchar(100)') as WebPage1,
c.profileName('(ProfileName)[0]','varchar(100)') as WebPage1,
c.value('(ProfileName)[0]','varchar(100)') as WebPage2,
none of these am I headed in the right direction?

What's the current node?

What's the relative path from the current node to ProfileName?

You're using the same relative path as the other nodes, which would make all of these nodes siblings. Is ProfileName a sibling of the other nodes?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mfink SSCommitted Points: 1632 More actions · Answer 14

Drew

I am not sure, I really don't know anything about XML