Update a table row with XML (SQL Server)

I have a xml file below generated using

SELECT * FROM case WHERE ticketNo=@ticketNo FOR XML RAW,
ELEMENTS

The XML looks like this:

<row>
  <ticketNo>1</ticketNo>
  <caller>name</caller>
  <category>3</category>
  <service>4</service>
  <workgroup>5</workgroup>
</row>

And I update my table using this query with the same with some value changed

UPDATE case
    set caller = xmldoc.caller
    set category = xml.category
    from OpenXml(@idoc, '/row')
    with (ticketNo VARCHAR(50)'./ticketNo', 
          caller VARCHAR(50开发者_如何学C) './caller', 
          category VARCHAR(50) './category') xmldoc
    where dbo.tb_itsc_case.ticketNo = xmldoc.ticketNo

Is it possible to update the table without specifying the individual column?

You can not do an update without specifying the columns and you can not get data from XML without specifying what nodes to get the data from.

If you can use the "new" XML data type that was introduced in SQL Server 2005 you can do like this instead.

declare @XML xml = 
'<row>
  <ticketNo>1</ticketNo>
  <caller>name</caller>
  <category>3</category>
  <service>4</service>
  <workgroup>5</workgroup>
</row>'

update [case] set
  [caller] = @XML.value('(/row/caller)[1]', 'varchar(50)'),
  category = @XML.value('(/row/category)[1]', 'varchar(50)')
where 
  ticketNo = @XML.value('(/row/ticketNo)[1]', 'varchar(50)')

I was able to use this to update 1 row in a table. It requires you to have all fields specified in the XML. It replaces the existing row with the one specified in the XML.

I wish I could figure out how to do it for only the columns that are in the XML. I work on projects where the fields in the table change frequently and it requires re-specifying all the procedures to list out the column names.

create procedure Update_TableName (@xml xml) as
DECLARE @handle INT
EXEC sp_xml_preparedocument @handle OUTPUT, @xml

DECLARE @ID varchar(255)
SELECT @ID = ID FROM OPENXML (@handle, '/data', 2) WITH TableName
if exists (select 1 from TableName where ID = @ID)
    delete from TableName where ID = @ID

Insert into TableName
SELECT * FROM OPENXML (@handle, '/data', 2) WITH TableName
EXEC sp_xml_removedocument @handle

XML:

<data>
<ID>9999</ID>
<Column1>Data</Column1>
<Column2>Data</Column2>
<Column3>Data</Column3>
</data>