• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

SQL: RDL Read::

Hello team,
How easy is to read an RDL file into an SQL table of choice?
I have over couple of hundred RDLs and I need to read the fields/values into a new table in SQL.. for further processings.

I know how to read an xml file into an sql table but having a bit of a difficulty with this critter.
any assistance will be apprecited.
thx

JohnE
0
John Esraelo
Asked:
John Esraelo
1 Solution
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
I have seen the following code in expert-exchange but it does not work for me.. not sure where I am missing certain pieces..
DECLARE @xml XML
SELECT @xml = BulkColumn 
FROM OPENROWSET(BULK 'C:\Bulk\Test Report.rdl', SINGLE_BLOB) AS x  
 
SELECT @xml;
 
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
--WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition')
 
SELECT 
	r.value('../../../../DataSources[1]/DataSource[1]/@Name','varchar(100)') AS DataSourceName,
	r.value('../../../../DataSources[1]/DataSource[1]/DataSourceReference[1]','varchar(10)') AS DataSourceReference,
	r.value('../../@Name','varchar(100)') AS DataSetName,
	r.value('@Name','varchar(100)') AS FiledName,
	r.value('../../Query[1]/CommandText[1]','varchar(max)') AS CommandText
--FROM @xml.nodes('Report/DataSets/DataSet/Fields/Field') AS doc(r)
FROM @xml.nodes('//Field') AS doc(r)

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now