Very Computer

I am trying to update a field in a database table using a stored procedure that
accepts an XML string.  Here is an example of the string:

<DATA><FIELDS><UNIT_NUMBER>1</UNIT_NUMBER><TERRITORY>77</TERRITORY></FIELDS>
<FIELDS><UNIT_NUMBER>2</UNIT_NUMBER><TERRITORY>72</TERRITORY></FIELDS></DATA>

I am trying to update rows that exist in a table with the <TERRITORY> value.  
However, I can't seem to do this.  I can't seem to parse through the XML to get
the territory value for unit_number 2.  Any ideas?

Dee,

It's very hard to determine what the problem is from this small amount of
information. Perhaps you could post the code that you are using to parse the
XML since that is most likely where the problem is at....

--
Bryant

Here is the XML string:

"<DATA><FIELDS><UNIT_NUMBER>1</UNIT_NUMBER><TERRITORY>72</TERRITORY></FIELDS>
<FIELDS><UNIT_NUMBER>2</UNIT_NUMBER><TERRITORY>77</TERRITORY></FIELDS></DATA>"

I wanted to use something like:

UPDATE vehicle
SET vehicle.territory = XMLFIELDS.territory

WITH ( UNIT_NUMBER tinyint, TERRITORY varchar(3) )
WHERE ...

This is where I can't specify my WHERE clause because I need to specify that

vehicle.unit_number = the unit_number in the XML clause.

Any ideas?

Quote:-----Original Message-----
Dee,

It's very hard to determine what the problem is from this small amount of
information. Perhaps you could post the code that you are using to parse the
XML since that is most likely where the problem is at....

--
Bryant

.

OK, here is another attempt to put the XML string out (Nodes will be in
parentheses!)

"(DATA)(FIELDS)(UNIT_NUMBER)1(/UNIT_NUMBER)(TERRITORY)72(/TERRITORY)(/FIELDS)
(FIELDS)(UNIT_NUMBER)2(/UNIT_NUMBER)(TERRITORY)77(/TERRITORY)(/FIELDS)(/DATA)"

Dee,

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

<ROOT>
<Employee EmployeeID="1" FirstName="Nancini"/>
</ROOT>'

update employees
set employees.FirstName = ox.FirstName

      WITH (EmployeeID  int, FirstName varchar(30)) ox
Where employees.employeeID = ox.employeeID

--
Bryant