Transcript Surviving_Parsing_XML_with_T-SQLx

Surviving parsing XML with
T-SQL
Steve Simon MVP SQL Server BI
http://www.infogoldusa.com







SQL Server MVP
Atrion Networking Corporation (Providence RI)
Involved with database design for 29 years +
Presenter at numerous PASS summits.
Presenter at numerous SQL Saturday events.
Presented at Copenhagen Nordic Rally in March.
Regular contributor on SQLShack.com
Facts of life
Data is not always stored in a simple format.
Often data is imported from xml documents.
Murphy’s law states that the data will be at the
bottom of the tree.
Parsing data from the tree is CPU consuming.
Today’s challenge
We need a list of reports that have been run
over the past 60 days.
We need the name of the report.
We need the dataset and data source name.
We need the fields that are displayed in the
report.
Let’s get
started
A practical case
Conversion of warehouse reports to run off of
“Service Now”.
Which reports are still active?
Who has used them and when?
Will the tables in “Service Now” contain the
necessary fields?
All of our data
resides in the
Report Server
Database
Selecting the Schema via a CTE
;WITH
XMLNAMESPACES
(DEFAULT
'http://schemas.microsoft.com/sqlserver/repo
rting/2008/01/reportdefinition'
,'http://schemas.microsoft.com/sqlserver/rep
orting/reportdesigner'
AS rd)
We shall be utilizing two tables
dbo.Catalog
dbo.ExecutionLogStorage
We execute the CTE
We convert
the content
field to XML
FROM (SELECT RPT.Path AS ReportPath
,RPT.name AS ReportName
,CONVERT(xml,
CONVERT(varbinary(max), RPT.content)) AS
contentXML FROM
[SQLSaturday386Albany].dbo.[Catalog] AS RPT
INNER JOIN
[SQLSaturday386Albany].dbo.ExecutionLogStora
ge Storage ON RPT.ItemID =
Storage.ReportID
WHERE RPT.Type = 2 –-i.e. a Report
) AS RPT
Content XML contains the ‘result set’
XML containing the field names
Type definitions
Cross Apply to get down the different branches
of the tree
Ascertaining the Dataset and Data Source Names
Fields within our reports
Data source, dataset and parameters passed
The query is also available from the report
Demo 1
Reporting
Demo 2
Take-Away
Why battle with XML
Parsing and processing XML data feeds.
Understanding the tree structure helps with
coding.
Techniques may be applied to reporting.
Getting access to critical data.
Surviving parsing XML with
T-SQL
Steve Simon MVP SQL Server BI
http://www.infogoldusa.com