June 2, 2014 at 11:16 am
Hi Guys
I'm fairly new to using SQLServers XML capabilities and I'm still trying to get my head around the best way to go about things.
The first question I have is about best practice. If I need to retrieve records where a certain value in an xml field exists (e.g. Last_Name = 'Hillier'), is that best phrased using .query or .exist. I can see a way of doing it with either syntax but I'm not sure what the implications are with regard to performance etc. I think I should be using .exist because that's what the msdn examples seem to use and I imagine that would allow the inner query to return fewer rows to the outer query but I could just do with someone confirming my thinking on that.
My second question would be best served by an example so here's a quick script to create and populate a table:-
Create Table Example (ID int, XML xml);
Insert into Example
Values (1, '<?xml-stylesheet type="text/xsl" href="Person.xsl" ?>
<Entity xmlns="http://tempuri.org/Ultimate" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://tempuri.org/Ultimate Party.xsd">
<Person>
<id>1</id>
<Title>Mr</Title>
<First_Name>Declan</First_Name>
<Last_Name>Hillier</Last_Name>
<Address_1>101 Romsey Road</Address_1>
<Address_2>Shirley</Address_2>
<Address_3>Southampton</Address_3>
<Address_4>Hants</Address_4>
<Postcode>SO16 4DD</Postcode>
</Person>
</Entity>')
Here's a select statement that should find that row:-
select *
from Party
Where [XML].exist('declare default element namespace "http://tempuri.org/Ultimate";
Entity[Person/Last_Name=''Hillier'' and Person/First_Name=''Declan'']')='true'
That works but for my purposes it would be useful if I could pass around whole xpaths, including the root element, and then combine them back together when I build the query. In other words I'd like to be able to phrase the query a bit more like this:-
select *
from Party
Where [XML].exist('declare default element namespace "http://tempuri.org/Ultimate";
[Entity/Person/Last_Name=''Hillier'' and Entity/Person/First_Name=''Declan'']')='true'
That would be easier for me to handle in the client but, of course, gives a syntax error because it doesn't have an element to evaluate the predicate against. Is there a way I can phrase the query while keeping the xpath "whole"?
Thanks in advance for any help.
June 2, 2014 at 12:22 pm
Is something like this what you are looking for?
😎
USE tempdb;
GO
;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/Ultimate')
,XML_DATA AS
(
SELECT
EX.ID
,PER.SON.value('id[1]' ,'INT') AS P_id
,PER.SON.value('Title[1]' ,'NVARCHAR(50)') AS P_Title
,PER.SON.value('First_Name[1]' ,'NVARCHAR(50)') AS P_First_Name
,PER.SON.value('Last_Name[1]' ,'NVARCHAR(50)') AS P_Last_Name
,PER.SON.value('Address_1[1]' ,'NVARCHAR(50)') AS P_Address_1
,PER.SON.value('Address_2[1]' ,'NVARCHAR(50)') AS P_Address_2
,PER.SON.value('Address_3[1]' ,'NVARCHAR(50)') AS P_Address_3
,PER.SON.value('Address_4[1]' ,'NVARCHAR(50)') AS P_Address_4
,PER.SON.value('Postcode[1]' ,'NVARCHAR(50)') AS P_Postcode
FROM dbo.Example EX
OUTER APPLY EX.XML.nodes('Entity/Person') AS PER(SON)
)
SELECT
XD.ID
,XD.P_id
,XD.P_Title
,XD.P_First_Name
,XD.P_Last_Name
,XD.P_Address_1
,XD.P_Address_2
,XD.P_Address_3
,XD.P_Address_4
,XD.P_Postcode
FROM XML_DATA XD
WHERE XD.P_First_Name = N'Declan';
Results
ID P_id P_Title P_First_Name P_Last_Name P_Address_1 P_Address_2 P_Address_3 P_Address_4 P_Postcode
--- ----- -------- ------------- ------------ ---------------- ------------ ------------ ------------ -----------
1 1 Mr Declan Hillier 101 Romsey Road Shirley Southampton Hants SO16 4DD
June 3, 2014 at 1:21 am
Not really. It brings the Element and Person parts of the path back together but now the First_Name is out in the breeze. The objective is to be able to keep the whole path together so I avoid having to parse bits out of it.
June 3, 2014 at 1:56 am
FunkyDexter (6/3/2014)
Not really. It brings the Element and Person parts of the path back together but now the First_Name is out in the breeze. The objective is to be able to keep the whole path together so I avoid having to parse bits out of it.
Now I get it:Whistling:
select *
from dbo.Example
Where [XML].exist('declare default element namespace "http://tempuri.org/Ultimate";
Entity[Person/Last_Name=''Hillier''] and Entity[Person/First_Name=''Declan'']')='true'
is much more effective than
select *
from dbo.Example
Where [XML].exist('declare default element namespace "http://tempuri.org/Ultimate";
Entity[Person/Last_Name=''Hillier'' and Person/First_Name=''Declan'']')='true'
as it results in a single table scan and a filter instead of rather a complex CLR implementation plan. For this sample, the difference is in the order of second or third magnitude.
😎
June 3, 2014 at 6:31 am
Here's a variant using FLOWR. I think it's a bit more readable but YMMV:
;with xmlnamespaces(DEFAULT 'http://tempuri.org/Ultimate')
select ex.xml.query('
for $x in /Entity
where $x/Person/Last_Name = "Hillier"
and $x/Person/First_Name = "Declan"
return $x')
from dbo.example ex
June 3, 2014 at 7:31 am
gbritton1 (6/3/2014)
Here's a variant using FLOWR. I think it's a bit more readable but YMMV:
;with xmlnamespaces(DEFAULT 'http://tempuri.org/Ultimate')
select ex.xml.query('
for $x in /Entity
where $x/Person/Last_Name = "Hillier"
and $x/Person/First_Name = "Declan"
return $x')
from dbo.example ex
This is even more expensive than the original query as it has more expensive looping in the lower levels of the plan, in addition to the CLR implementation.
😎
June 3, 2014 at 10:36 am
That's pretty much what I want syntactically but Eric's comments on performance are welcome. The next task on my list for developing this is to scale it up and stress test it so that'll give me the perfect chance to try both approaches out.
Thank you both for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply