OpsMgr 2007 SSRS Reports using SQL 2005 XML

This post relates mostly to the concept of passing XML between SQL Server Reporting Services (SSRS), Operations Manager Smart Controls, and SQL Stored Procedures when you are customising Operations Manager 2007 reporting.

Passing multi-valued parameters from the Operations Manager Smart Controls through the SSRS report to SQL can be difficult, and the method employed most by the default Microsoft reports seems to be XML.

SQL 2005 includes the XML data type and associated methods, including XQuery, a subset of the XPath query standard. In addition, SQL supports OPENXML – a rowset provider to construct a relational rowset view of an XML document.

Included below are two examples of processing an XML string and querying an element value from the XML elements with a SQL insert/select clause.

declare @execerror int
declare @xmldoc xml
declare @ixmldoc int
set @xmldoc = '<Data><Objects><Object Use="Containment">376</Object><Object Use="Containment">300</Object></Objects></Data>'
DECLARE @tblTest table (test int)


/* Parse the XML document and insert the converted int <object> element value into a temporary table */

EXEC @ExecError = sp_xml_preparedocument @ixmldoc OUTPUT, @xmldoc
INSERT INTO @tblTest
SELECT * FROM
OPENXML (@ixmldoc, '/Data/Objects/Object')
WITH (InstanceFilter int '.')

select * from @tbltest


/* Translate the value of <object> nodes to an int from an XML document into the test field of the table */

INSERT INTO @tblTest (test)
select tblTest.test.value('.', 'int')
from @xmldoc.nodes('/Data/Objects/Object') AS tblTest(test)

select * from @tbltest


Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.