November 2, 2011 at 11:20 am
Folks:
I have this XML data in one of the column (Reporting services Report Data). I want to shred the data and get the following information from this XML data and store it in a table. Can anybody help me a query to pull the following data?
<Subreport Name="">
<ReportName>
<DataSets>
<DataSet Name=" ">
<DataSourceName>
<CommandText>
November 2, 2011 at 11:36 am
Your XML is malformed, probably because of copy-and-paste into a website. Take a look at line 1045. Getting an "Illegal character" error on that line when I try to consume the XML.
Try posting it as an attached txt file instead. Should work better.
I'll see what I can do with it if I clean it up a bit manually, but that won't get you precisely what you need, since I won't be testing it on the data you're actually using.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:43 am
This should get you started:
DECLARE @X XML = '...copy your XML here...';
SELECT X.SR.query('.').value('declare namespace RS="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";(/RS:Subreport/RS:ReportName/text())[1]',
'varchar(1000)')
FROM @X.nodes('declare namespace RS="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";//RS:Subreport') X (SR);
Use that template to get each of the values you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:45 am
Oh, and please do remove all that XML from your original post and include it as an attachment. It's slowing down the page-render so badly I actually had trouble replying to you without my browser locking up. Just as a favor, please do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 12:52 pm
The query works great... The only column which I am not able to shred is the <DataSet Name="">. Any help on this is appreciated.
-<DataSets>
+<DataSet Name="GetLastCalendarDate">
+<DataSet Name="PrevDate">
+<DataSet Name="DeliverhardCopy">
+<DataSet Name="PORDropDown">
</DataSets>
November 2, 2011 at 1:01 pm
That one's not the text property, it's a value. Has to be queried differently:
SELECT X.SR.query('.').value('declare namespace RS="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";(/RS:DataSet/@Name)[1]',
'varchar(1000)')
FROM @X.nodes('declare namespace RS="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";//RS:DataSet') X (SR)
Changed from using "RS:DataSet/RS:Name/text()" to using "RS:DataSet/@Name".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 1:12 pm
Thanks for your help !
November 2, 2011 at 2:21 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply