November 5, 2015 at 6:52 am
Hi all,
It's official, me and XML don't get on, at least not when I have to query it. So I'm after a bit of help please. I've attached a zip file which contains a SQL script
to declare a table variable, populate it with some test XML data and then a SELECT statement which is supposed to (but doesn't) extract the 'DataSet' text - The SELECT statement right near the top of the XML.
I'd be very grateful if someone could take a look and tell me what I'm doing wrong here.
Thanks,
Nic
November 5, 2015 at 7:37 am
Taken from the following url https://gallery.technet.microsoft.com/scriptcenter/List-datasets-with-command-ddffefde
And changed to fit your sample script and namespaces.
;WITH
XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
AS rd)
,DEF AS
(SELECT
REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)')))
,'>', '>')
,'<', '<')
AS CommandText
FROM (SELECT CONVERT(xml, CONVERT(varbinary(max), RPT.TestXML)) AS contentXML
FROM @TblVarXML AS RPT
--WHERE RPT.Type = 2 -- 2 = Reports
) AS RPT
CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)
)
SELECT
DEF.CommandText
FROM DEF
November 5, 2015 at 8:04 am
Thank you so much for not only the solution but also for the link.
I am very grateful for both. I will sleep easier tonight.
Thanks,
Nic
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply