How can I specify a where clause to join an OPENXML statement with a table? (12625 Requests)

Here is a small sample that you can run on the Northwind database which does what you are trying to do.

DECLARE @idoc int, @doc varchar(1000)
SET @doc ='
<ROOT>
<Employee EmployeeID="1" FirstName="Nancini"/>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

update employees
set employees.FirstName = ox.FirstName
FROM OPENXML (@idoc, '/ROOT/Employee')
      WITH (EmployeeID  int, FirstName varchar(30)) ox
Where employees.employeeID = ox.employeeID

EXEC sp_xml_removedocument @idoc

Feedback

# Performing an update using OPENXML with a metaproperty 1/18/2002 1:53 PM Melissa Whalen

How can update a table that contains XML data using @mp:XMLText? (I've tried to do it already, but it only puts a blank field in where the XML should be.)

# Performing an update using OPENXML with a metaproperty 3/13/2002 10:16 AM Brad Smalling

I've had the same problem. INSERTing a TEXT field with @mp:xmltext works fine. The exact same UPDATE will put a blank string (not NULL) in the TEXT field and not report an error. I've tried everything I can think of--it's gotta be a bug. CHARs and VARCHARS work fine for both INSERT and UPDATE. Only TEXT and NTEXT columns fail to UPDATE properly.

# inserting or updating xml data in to SQL server 7 5/26/2003 8:52 AM vijaya

we are using Sql server 7.0. Is there any way to use the xml string in stored procedure to update and insert records. I have created the xml string using xmlDOM in asp, but openxml functionality isn't available in sql 7, so I don't how to insert the data

# re: How can I specify a where clause to join an OPENXML statement with a table? 11/14/2006 8:57 AM CS

How can I have IF clause when retreiving data from XML using OPENXML, so that I can do something like...

IF @Name = 'TEST'

BEGIN
Do some work here...before inserting to the table....

END

Here the @Name gets the value from XML file stored in the database using OpenXML

Any help would be appreciated.

Thanks...

- webchetan

# ftmpqg frnpj 11/4/2007 11:17 PM qewshncdf@mail.com

tuienfo wyekv zljayqp akmngdj kcqpdfizg ohbvpm olpgrvtj