September 28, 2015 at 7:06 am
Hi all,
I wondered if someone could help with something. I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
Can someone help me out please?
Thanks,
Nic
create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT GETDATE()
</CommandText>
</Query>
</DataSet>
</SharedDataSet>')
select field1.query('/SharedDataSet/DataSet/Query/CommandText') from #demo
select field1.query('/DataSet/Query/CommandText') from #demo
drop table #demo
September 28, 2015 at 7:26 am
Xquery must contain namespace declaration as it is dealing with namespaced xml.
select field1.query('declare namespace x="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition";
/x:SharedDataSet/x:DataSet/x:Query/x:CommandText') from #demo
September 28, 2015 at 7:47 am
Hi,
That seems to have done the job nicely.
Thank you very much, Querying XML is a bit of a dark art to me so you've made my life far easier.
Nic
September 28, 2015 at 8:15 am
You are welcome.
September 28, 2015 at 11:05 am
Quick thought, the posted solution works but it imposes quite a lot of work which can be avaided, here is a more efficient alternative.
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'sbo..#demo') IS NOT NULL DROP TABLE #demo;
create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT GETDATE()
</CommandText>
</Query>
</DataSet>
</SharedDataSet>')
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS RD
,DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition')
SELECT
D.field1.value('(SharedDataSet/DataSet/Query/CommandText/text())[1]','VARCHAR(50)') AS CommandText
FROM #demo D;
Result
CommandText
-----------------
SELECT GETDATE()
September 28, 2015 at 12:22 pm
To optimize a bit further why declare namespace which is never used in the query
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition')
SELECT
D.field1.value('(SharedDataSet/DataSet/Query/CommandText/text())[1]','VARCHAR(50)') AS CommandText
FROM #demo D;
September 28, 2015 at 1:10 pm
serg-52 (9/28/2015)
To optimize a bit further why declare namespace which is never used in the query
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition')
SELECT
D.field1.value('(SharedDataSet/DataSet/Query/CommandText/text())[1]','VARCHAR(50)') AS CommandText
FROM #demo D;
Since it has no effect on the query performance and the purpose was to demonstrate the XMLNAMESPACES declaration, I thought it was appropriate to include it.
😎
The difference between the two method isn't too noticeable when querying a single value but when working with larger sets it is.
Quick test, changed the query to return varchar(50) in order to level the playing field.
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'sbo..#demo') IS NOT NULL DROP TABLE #demo;
create table #demo (field1 xml)
insert into #demo (field1)
SELECT
TOP (100000) ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT GETDATE()
</CommandText>
</Query>
</DataSet>
</SharedDataSet>')
FROM sys.all_columns SAC1
CROSS JOIN sys.all_columns SAC2;
DECLARE @CHAR_BUCKET VARCHAR(50) = '';
DECLARE @XML_BUCKET XML = NULL;
raiserror('-----DRY RUN-------------------------',0,0) with nowait;
SELECT
@XML_BUCKET = D.field1
FROM #demo D
raiserror('-----METHOD 1-------------------------',0,0) with nowait;
set statistics io,time on;
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS RD
,DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition')
SELECT
@CHAR_BUCKET = D.field1.value('(SharedDataSet/DataSet/Query/CommandText/text())[1]','VARCHAR(50)')
--,D.field1
FROM #demo D
;
set statistics io,time off;
raiserror('-----METHOD 2-------------------------',0,0) with nowait;
set statistics io,time on;
select @CHAR_BUCKET = field1.value('declare namespace x="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition";
(/x:SharedDataSet/x:DataSet/x:Query/x:CommandText)[1]','VARCHAR(50)')
from #demo
;
set statistics io,time off;
raiserror('-----METHOD 3-------------------------',0,0) with nowait;
set statistics io,time on;
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition')
SELECT
@CHAR_BUCKET = D.field1.value('(SharedDataSet/DataSet/Query/CommandText/text())[1]','VARCHAR(50)')
--,D.field1
FROM #demo D
;
set statistics io,time off;
Results
-----DRY RUN-------------------------
-----METHOD 1-------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#demo_____________________________________
SQL Server Execution Times:
CPU time = 1450 ms, elapsed time = 395 ms.
-----METHOD 2-------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.
Table '#demo_____________________________________
SQL Server Execution Times:
CPU time = 2354 ms, elapsed time = 594 ms.
-----METHOD 3-------------------------
SQL Server parse and compile time:
CPU time = 2 ms, elapsed time = 2 ms.
Table '#demo_____________________________________
SQL Server Execution Times:
CPU time = 1514 ms, elapsed time = 391 ms.
The reason for the difference is mainly that when using the text() function, there is only one XML Reader operator in the execution plan, otherwise there are three.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply