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
John EsraeloDatabase / SQL DeveloperAsked:
Who is Participating?
 
John EsraeloConnect With a Mentor Database / 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.