Check if value exists in xml node

Question

I have the following XML

    <?xml version="1.0"?>
    <ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
     xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="id">
        <int>984026</int>
        <int>1005222</int>
    </ArrayOfInt>

I pass this to a store procedure with an XML Parameter.

I want to return all the rows that the id is in the int nodes from xml doc

    SELECT * FROM
    MyTable
    WHERE
    MyTable.Id IN @XMLDoc.value('(.)[1]', 'int')

This only works if I send one int in the xml

    <?xml version="1.0"?>
    <ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="id">
        <int>984026</int>
    </ArrayOfInt>

But if I run this with the two int nodes, sql concatenates the nodes

The conversion of the nvarchar value '9840261005222' overflowed an int column.

what database are we talking about?

stefaan dutry
– stefaan dutry

2014-01-12 23:10:30 +00:00
Commented Jan 12, 2014 at 23:10 — stefaan dutry
– stefaan dutry, Commented Jan 12, 2014 at 23:10

DimaSUN · Accepted Answer · 2014-01-13 00:46:35Z

1

    declare @XMLDoc xml = '<?xml version="1.0"?>
           <ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
               xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="id">
               <int>984026</int>
               <int>1005222</int>
           </ArrayOfInt>'

    ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as xsi ,
                'id' as ns2,
                DEFAULT 'id')
    SELECT MT.* 
    FROM MyTable
    join @XMLDoc.nodes('/ArrayOfInt/int') T(z) 
    on MT.ID = T.z.value('.','int')

answered Jan 13, 2014 at 0:46

DimaSUN

9218 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1416072 Over a year ago

Doesnt works for me, I get no results, the only way I get any row as a result is by changing "join @XMLDoc.nodes('/ArrayOfInt/int') T(z)" for "join @XMLDoc.nodes('.') T(z)" but it only works when I have one int node in the xml. When I have two nodes or more, sql concatenates them and keeps throwing "The conversion of the nvarchar value '9840261005222' overflowed an int column." By the way, I didnt put the "WITH XMLNAMESPACES" as I don't declare the xml, I get it as a parameter.

user1416072 Over a year ago

Getting rid of 'xmlns="id"' solved the problem using your answer, thanks!

Collectives™ on Stack Overflow

Check if value exists in xml node

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related