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