Update using XML in SQL Server

Question

I am trying to update a table using an xml.

UPDATE dbo.M_Picture
SET Sequence = T.c.query('Sequence')
FROM dbo.M_Picture pic
INNER JOIN @xml.nodes('/pictures/picture') T(c)
ON pic.PictureId = T.c.query('pictureId') --I guess issue is in this line

The XML I am using is

<pictures>
  <picture>
    <pictureId>30</pictureId>
    <Sequence>4</Sequence>
  </picture>
  <picture>
    <pictureId>31</pictureId>
    <Sequence>4</Sequence>
  </picture>
</pictures>

The error message I am getting is

Operand type clash: xml is incompatible with int

And it makes sense because in

pic.PictureId=T.c.query('pictureId')

pictureId is int

How to sort this out?

marc_s · Accepted Answer · 2012-07-22 20:18:31Z

2

Try this:

UPDATE dbo.M_Picture
SET Sequence = T.c.query('Sequence')  -- this here *also* assign an XML fragment to "Sequence"
FROM dbo.M_Picture pic
INNER JOIN @xml.nodes('/pictures/picture') T(c)
ON pic.PictureId = T.c.value('(pictureId)[1]', 'int')

Use the .value() method on the XML - that returns a scalar type (like int)

Also: I'm not sure if you really want to assign the XML fragment <Sequence>4</Sequence> to your column Sequence - or whether you want to use the .value() method there, too!

edited Jul 22, 2012 at 20:18

answered Jul 22, 2012 at 19:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Update using XML in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related