Update table with XML string PROBLEMS

Update table with XML string PROBLEMS

Post by Dee Rogheli » Thu, 07 Jun 2001 02:50:04



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?

 
 
 

Update table with XML string PROBLEMS

Post by Bryant Like » Thu, 07 Jun 2001 03:54:09


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

 
 
 

Update table with XML string PROBLEMS

Post by Dee Rogheli » Thu, 07 Jun 2001 20:53:59


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

.

 
 
 

Update table with XML string PROBLEMS

Post by Dee Rogheli » Thu, 07 Jun 2001 21:04:46


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)"

-----Original Message-----

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?
-----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

..

.

 
 
 

Update table with XML string PROBLEMS

Post by Bryant Like » Fri, 08 Jun 2001 03:19:09


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

 
 
 

1. Transform XML/XSLT dynamically from sp (XML string)

Hi,
I am looking at transforming a XML string returned from a
sp with XSLT. I want to load the string dynamically
without first having to save the string as a XML file.
I cannot seem to find any help on this topic. All the
examples that I have come across load the XML string from
a file.

Which is the best way to do this?

Thanks
Jason

2. Supplement from the author...

3. Howto convert tokenized XML to XML String?

4. How to Create Holidays Table?

5. XML Bulk Load - DTS Connection String problem

6. databases

7. Updating tables with XML

8. subscribe

9. Updating tables with XML in SQL Server

10. How can update or insert into the table according to XML recordset file

11. Updating tables with xml in SQL Server

12. Updating tables using XML

13. Updating SQL Tables from XML Documents