1

My input xml string is 

<users><id>p1</id><id>p2</id><id>p3</id></users>

i want to write an update query to update the column 'Request_Status' in my table if the id matches to the ones in the xml string

I tried this statement:

Update login set 
            Request_Status='A'
            where
            EmpId in
            (
                SELECT Pers.value('(id)[1]', 'nchar(10)') as 'ID'
                FROM
                        @xmlUserId.nodes('/users') as EMP(Pers)
            )

This only updates for ID p1 and not for the other 2.

I referred to this,

How to loop and parse xml parameter in sql server stored procedure

(P.S my first question here, so please excuse mistakes, unconventional elements in the post)

Community
  • 1
  • 1
Shounak Gujarathi
  • 51
  • 1
  • 7

1 Answers1

0

Try to change the XML selection query to be as follow :

SELECT Pers.value('self::*', 'nchar(10)') as 'ID'
FROM
        @xmlUserId.nodes('/users/id') as EMP(Pers)

Above query should return all <id> element within <users> element

[SQL fiddle demo]

har07
  • 88,338
  • 12
  • 84
  • 137
  • @ShounakGujarathi see the demo, it works given input XML string you posted. Create a new fiddle to demonstrate how this doesn't work so we can check further – har07 Nov 05 '14 at 07:34
  • Yes, it seems to be working in the fiddle. I can't add an Update Statement there though.. ( Update login set Request_Status='A' where EmpId in ( SELECT Pers.value('self::*', 'nchar(20)') FROM @xmlUserId.nodes('/users/id') as EMP(Pers) ); – Shounak Gujarathi Nov 05 '14 at 09:30
  • Dude nvm, i restarted sql server and it worked. Thanks a lot. – Shounak Gujarathi Nov 05 '14 at 09:41