July 25, 2011 at 2:46 pm
If you're going to be working with XML, you need to learn XPath. There is a tutorial at W3Schools http://www.w3schools.com/xpath/. XPath is very easy.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 26, 2011 at 8:21 am
A completed version of the problem and solution
Declare @x xml
SET @x='<?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.BigRedFiretruckwebpage.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>
'
SELECT
c.value('(//AnalyticsReport/Report/Title/ProfileName)[1]','varchar(100)') as WebPage,
c.value('(PrimaryKey)[1]', 'varchar(30)') AS Date
, c.value('(DrilldownId)[2]', 'varchar(25)') AS Region
, c.value('(Cell/Content/Value)[1]', 'int') AS Counts
FROM @x.nodes('/AnalyticsReport/Report/Table/Row') AS T(c)
WebPage DateRegionCounts
http://www.BigRedFiretruckwebpage.comMonday, July 18, 2011Virginia 4
http://www.BigRedFiretruckwebpage.comTuesday, July 12, 2011Pennsylvania3
http://www.BigRedFiretruckwebpage.comThursday, July 14, 2011Virginia3
http://www.BigRedFiretruckwebpage.comMonday, July 18, 2011Wisconsin3
http://www.BigRedFiretruckwebpage.comWednesday, July 13, 2011Washington2
http://www.BigRedFiretruckwebpage.comThursday, July 14, 2011Washington2
http://www.BigRedFiretruckwebpage.comTuesday, July 12, 2011Washington1
http://www.BigRedFiretruckwebpage.comWednesday, July 13, 2011Illinois1
http://www.BigRedFiretruckwebpage.comFriday, July 15, 2011Virginia1
http://www.BigRedFiretruckwebpage.comFriday, July 15, 2011Wisconsin1
http://www.BigRedFiretruckwebpage.comSunday, July 17, 2011District of Columbi1
http://www.BigRedFiretruckwebpage.comSunday, July 17, 2011Virginia1
http://www.BigRedFiretruckwebpage.comMonday, July 18, 2011Maryland1
http://www.BigRedFiretruckwebpage.comMonday, July 18, 2011Florida1
http://www.BigRedFiretruckwebpage.comMonday, July 18, 2011Washington1
July 26, 2011 at 1:18 pm
mfink (7/26/2011)
A completed version of the problem and solutionSELECT
c.value('(//AnalyticsReport/Report/Title/ProfileName)[1]','varchar(100)') as WebPage,
c.value('(PrimaryKey)[1]', 'varchar(30)') AS Date
, c.value('(DrilldownId)[2]', 'varchar(25)') AS Region
, c.value('(Cell/Content/Value)[1]', 'int') AS Counts
FROM @x.nodes('/AnalyticsReport/Report/Table/Row') AS T(c)
This uses an absolute path, since it begins with a "/", for the ProfileName. You generally want to use a relative path. Using the absolute path will always pull the FIRST instance (as specified by the index) instead of pulling the instance that corresponds to your current node. I don't know if your actual files might contain multiple reports, but it certainly can't hurt to modify it to use a relative path.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 26, 2011 at 1:22 pm
Only one Report per file. So the first instance will be ok. In this case.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy