0

By following question: See if XML element exists at any level in document with a specific value

Is there a possible solution to implement like clause within exist?

For example I have similar structure of XML as in question but in "tag" :<ContactLName>somevalues;Brandt;anothervalues</ContactLName> "Brandt" stored a little different. To find "Btandt" I have to use something like like, but I didn't find anything.
Here is an example what I am looking for:
WHERE tmp.[XmlCol].exist(N'/*/ContactLName[text() like "%Brandt%"]') = 1;

Edit(XML): 
<event>
<id>23456</id>
<status_in_knowledge_map>active</status_in_knowledge_map>
<custom_elems>
    <custom_elem>
        <name>f_n84u</name>
        <value>6769747818850362088;6622044220776915827;5370802024842340642;6583457400600680603;6724970265512462551;6954452686001110038;6667448592984922570;6390613356250088198;6985638718331227440;5370802050233479119;5370802024842340639;6208275408034531857;6078654416541145539;6585528811993904287;6805915353243667957;6842010739999728536;6964839747571514100;6491764192266708067;6630060828301937234;6964682936739957053;6996382008379854236;5703859111941721724;6780468964682252783;</value>
    </custom_elem>
    <custom_elem>
        <name>f_twxr</name>
        <value>7031439420534698908;5667697629116916565;5667697629116916521;7032219545122506576;7032219609944822365;7032219012824308287;7032219395780000352</value>
    </custom_elem>
</custom_elems>
<asd>adsf</asd>
Improve this question

2 Answers 2

Reset to default
1

You can use fn:contains in the predicate.

c.data.exist('/course/custom_elems/text()[fn:contains(., "7032219868543590434")]') = 1;
Improve this answer
3
  • I've tried your solution, but unfortinatelly it shows an empty result. With the .value method it works. Maybe exact XML structure will give a little help to visualize the problem, I will edit my question and add the sctructure.
    – Fatdrfrog
    Commented Nov 23, 2021 at 9:14
  • @Fatdrfrog Looks like it is working for me. Have a look here. dbfiddle.uk/… Of course I had to change your XML a bit and use a value that is actually in there and adapt the XQuery to the new structure.
    – Mikael Eriksson
    Commented Nov 23, 2021 at 17:27
  • yap, it works. I've tried exist by finding node() and it worked perfect. I even combined value and contains by node(): SELECT e.data.value('(/event/custom_elems/node()[fn:contains(., "f_n84u")])[1]','VARCHAR(MAX)') as df; and it found needed value.
    – Fatdrfrog
    Commented Nov 30, 2021 at 12:00
-1

I found an answer by using value():

WHERE c.data.value('(/course/custom_elems)[1]','VARCHAR(MAX)') LIKE '%7032219868543590434%'

Question is probably a dublicate and has an answer here.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.