Post reply

Use LIKE in SQL SERVER to XML record

  • December 4, 2014 at 1:54 am

    #100198

    Hi ,

    I have records in a table that contain elements as below (<c184>). My problem is that I cannot use the LIKE operator.

    I use WHERE XMLRECORD.exist('(/row/c184[contains(.,"141024")])')=1 to find records but this statement checks the whole value. I would like to know how I can search the below to find records that element 184 contains value that starts with 141024.

    If I had used WHERE XMLRECORD.exist('(/row/c184[contains(.,"141024")])')=1 then it would have returned all the records below. The result should be only the last record (3)

    record 1

    <c184>0813141024</c184>

    <c184 m="2">0814141024</c184>

    <c184 m="3">0815141024</c184>

    record 2

    <c184>0811141024</c184>

    <c184 m="2">0810141024</c184>

    <c184 m="3">0819141024</c184>

    record 3 ([highlight="#F2F5A9"]141024[/highlight]0822)

    <c184>1411140843</c184>

    <c184 m="2">1410240822</c184>

    <c184 m="3">1410230822</c184>

    Thanks

  • December 22, 2014 at 12:16 pm

    #1766052

    I believe this could be done more efficiently using the XQuery Substring method but I could not quickly figure out how.

    In the meantime, here's a solution...

    -- (1) create your sample data

    DECLARE @yourtable table (xid int, xmlrecord xml)

    INSERT @yourtable

    SELECT 1, '

    <c184>0813141024</c184>

    <c184 m="2">0814141024</c184>

    <c184 m="3">0815141024</c184>'

    UNION ALL

    SELECT 2, '

    <c184>0811141024</c184>

    <c184 m="2">0810141024</c184>

    <c184 m="3">0819141024</c184>'

    UNION ALL

    SELECT 3, '

    <c184>1411140843</c184>

    <c184 m="2">1410240822</c184>

    <c184 m="3">1410230822</c184>';

    --SELECT * FROM @yourtable

    -- (2) Solution

    WITH nodes AS

    (

    SELECT xid, xmlrecord, xx = xx.value('.', 'varchar(100)')

    FROM @yourtable

    CROSS APPLY xmlrecord.nodes('c184') x2(xx)

    ),

    filter AS

    (

    SELECT xid

    FROM nodes

    WHERE xx LIKE '141024%'

    GROUP BY xid

    )

    SELECT t.xid, xmlrecord

    FROM @yourtable t

    JOIN filter f ON t.xid=f.xid;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • January 9, 2015 at 5:25 am

    #1769036

    Thanks a lot. That's what I needed

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply