Querying XML as a Where statement - Query or Exist and how to combine two paths

  • 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.

  • 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

  • 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.

  • 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.

    😎

  • 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

  • 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.

    😎

  • 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