XQuery Starts-with alternative

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

XQuery Starts-with alternative

Author

Topic

afzaal820
Starting Member

19 Posts

Posted - 2011-06-11 : 13:19:08

how to implement alternative of starts-with and ends-with without singleton (value function).

I know one alternative would be like

declare @Xml xml

set @Xml = '<demo>
<nodes>
<node>
<age>12.0</age>
<info>atest1</info>
<dob>2011-01-01</dob>
<worktime>10:00:00</worktime>
</node>
<node>
<age>14</age>
<info>test2</info>
<dob>2011-01-02</dob>
<worktime>13:00:00</worktime>
</node>
</nodes>
</demo>'

Create table #TempTable
(
rowid int identity(1,1),
xmldata xml
)

INSERT #TempTable(xmldata) VALUES (@Xml)

select * FROM #TempTable
WHERE xmldata.value('(/demo/nodes/node/info[text()])[1]', 'nvarchar(max)') Like 'ate%'

Drop table #TempTable

but i want something like:

select * FROM #TempTable
WHERE xmldata.exists('/demo/nodes/node/info[text() like "%ate"]') = 1

I know it is not the xquery standard but anyotherway to do it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 13:32:18

You can use the contains function like this:


select * FROM #TempTable 
WHERE xmldata.exist('/demo/nodes/node/info/text() [contains(., "ate")]') = 1

Alternatively, you can shred the elements down to the info node and then use the value function:


select 
   tt.*
from 
   #TempTable tt
   cross apply tt.xmldata.nodes('/demo/nodes/node/info') T(c)
where
   c.value('.', 'nvarchar(max)') Like 'ate%'

Regardless of which method/how you do it, value function will make you write the code to make sure that it gets a singleton value.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 13:48:53

Ignore my first suggestion above. You want it to START with the search string. Contains function will just return true if the search string occurs anywhere in the string.

There is a "starts-with" function in W3C xQuery 1.0 Language Specification, but SQL implementation of xquery does not have that.

afzaal820
Starting Member

19 Posts

Posted - 2011-06-11 : 14:03:38

Thanks a lot, contains function gives incorrect results however other alternative can work but not in my senario, may be you have better suggestions e.g.

Actually I am searching in a big xml where the user can select any node or any number of nodes: so the later solution goes like that

select
tt.*
from
#TempTable tt
cross apply tt.xmldata.nodes('/demo/nodes/node/info') T(c) cross apply tt.xmldata.nodes('/demo/nodes/anothernode/remarks') T(d)
...(so on)
where
c.value('.', 'nvarchar(max)') Like 'ate%' AND d.value('.', 'nvarchar(max)') Like 'abc%'

and in real senario I am generating the query dynamically so I can afford adding conditions in where clauses but not cross apply(I dont know whether it is syntactically correct). Multiple select statments with union operation will not work as in my case these conditions are part of expression like ((value for node 1 starts with ) AND (value for node 2 ends with). I hope you can understand with my description if not please let me know.

Please help if any alternative in mind.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 14:38:22

Would shredding down to only the node level in the cross apply and then changing the where clause to query within that node to pick up the appropriate element work for you? What I mean is something like this:


select 
   tt.*
from 
   #TempTable tt
   cross apply tt.xmldata.nodes('/demo/nodes/node') T(c)
where
   c.value('(./info)[1]', 'nvarchar(max)') Like 'ate%'
   and c.value('(./remarks)[1]', 'nvarchar(max)') Like 'abc%'

Here you would have only one cross apply, but multiple conditions in the where clause.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 15:01:33

If you will be doing a lot of querying and if the xml is large, the performance is likely to be poor. If that is the case, and if you have a choice, it may be worthwhile to shred the data and store in a relational table. That will make querying much simpler and faster.

The alternative is to add XML indexes. You can add a primary index and 3 different types of secondary indexes. The primary index (which is a pre-requisite for the other types of indexes) is really storing a shredded version of the xml in the index. So it will take up lot of space. If you have to do that anyway, if the business scenario permits it, you might as well do the shredding yourself and store a shredded version of the xml data.

I know you didn't ask for a lecture, but I am in the office, working on a beautiful Saturday when everyone I know is out having fun. So I need someone to lecture to, and you just happened to be in the wrong place at the wrong time. But, I will get off from the soapbox now

afzaal820
Starting Member

19 Posts

Posted - 2011-06-12 : 11:30:04

Thanks a lot, I have already done with indexing both primary and secondary on xml data type. Along with that the mostly searchable nodes are manual indexed to the column so that we can directly look the value from there rather then xml. Also do some custom implementation for multiple value fields (That has further child levels) but the user still can search from XML.

Also I like your suggestion of fixing the cross apply section to specific level but it will not solve the problem as if searchable node is in different xpath. (I hope you understood the problem). Also by doing so it will be searching for the single value not all the xpath nodes.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-12 : 14:41:44

I am not quite sure I followed your explanation, but from your description of the various strategies you have tried, it seems like you have explored this quite a bit and are running into the limitations of Microsoft's implementation of xquery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources