Update SQL Server table using XML data

Question

From my ASP.Net application I am generating XML and pass it as input data to stored procedure as below,

<Aprroval>
  <Approve>
    <is_nb_approved>false</is_nb_approved>
    <is_approved>true</is_approved>
    <is_submitted>true</is_submitted>
    <UserId>35</UserId>
    <ClientId>405</ClientId>
    <taskDate>2015-05-23T00:00:00</taskDate>
  </Approve>
  <Approve>
    <is_nb_approved>false</is_nb_approved>
    <is_approved>true</is_approved>
    <is_submitted>true</is_submitted>
    <UserId>35</UserId>
    <ClientId>405</ClientId>
    <taskDate>2015-05-24T00:00:00</taskDate>
  </Approve>
</Approval>

And below is my stored procedure,

create procedure UpdateTaskStatus(@XMLdata XML)
  AS

UPDATE [TT_TaskDetail] 
SET 
    is_approved=Row.t.value('(is_approved/text())[1]','bit'),
    is_nb_approved=Row.t.value('(is_nb_approved/text())[1]','bit'),
    is_submitted=Row.t.value('(is_submitted/text())[1]','bit')
FROM @XMLdata.nodes('/Aprroval/Aprrove') as Row(t)
WHERE user_id = Row.t.value('(UserId/text())[1]','int') 
  AND client_id = Row.t.value('(ClientId/text())[1]','int') 
  AND taskdate = Row.t.value('(taskDate/text())[1]','date')

But when I execute this stored procedure, I am getting return value as 0 and no record is getting updated. Any suggestions welcome.

Did you try just selecting from that xml?

Giorgi Nakeuri
– Giorgi Nakeuri

2015-06-15 19:18:54 +00:00
Commented Jun 15, 2015 at 19:18 — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Jun 15, 2015 at 19:18
Yes, not returning anything

Sangeetha
– Sangeetha

2015-06-15 19:24:56 +00:00
Commented Jun 15, 2015 at 19:24 — Sangeetha
– Sangeetha, Commented Jun 15, 2015 at 19:24

Giorgi Nakeuri · Accepted Answer · 2015-06-15 19:45:39Z

1

You have 2 errors in your xml:

First is nonmatching root tags.

Second, more important, you are quering nodes('/Aprroval/Aprrove'), but inner tag is Approve not Aprrove.

Fiddle http://sqlfiddle.com/#!3/66b08/3

answered Jun 15, 2015 at 19:45

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

Comments

DeadZone · Accepted Answer · 2015-06-15 19:38:08Z

1

Your outer tags do not match. Your opening tag says, "Aprroval" instead of "Approval". Once I corrected that, I was able to select from the XML without issue.

answered Jun 15, 2015 at 19:38

DeadZone

1,6891 gold badge18 silver badges34 bronze badges

Collectives™ on Stack Overflow

Update SQL Server table using XML data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related