Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Updating with OPENXML
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 01/24/2002 :  09:20:46  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Melissa writes "Hi,
I've written a stored procedure that I'm trying to use to update a database. The SP uses OPENXML as the data I want to put into the database is an XML file that could be quite large. I've found very brief examples of updating with OPENXML and I have been able to INSERT using an almost identical stored procedure, but the update doesn't seem to work the same.
Here is the SP:
CREATE PROCEDURE updateXML (@pDoc text, @ReportID int)
DECLARE @rDoc int

EXEC sp_xml_preparedocument @rDoc OUTPUT, @pDoc

UPDATE tempdata
SET CRASH_REPORT_NUM = xmlFile.FormID, datReport = xmlFile.newDatReport, County_City_Code = xmlFile.newCountyCityCode, Make1 = xmlFile.newMake, Owner_Name = xmlFile.newOwnerName, form = xmlFile.newForm
FROM OPENXML(@rDoc, '/formtransmit', 2)
newDatReport datetime 'form/Date',
newCountyCityCode numeric 'form/COUNTY_CITY_CODE',
newMake char(10) 'form/MAKE1',
newOwnerName varchar(40) 'form/Owner_Name',
newForm text '@mp:XMLtext')) xmlFile
WHERE tempData.Report_ID = @ReportID

EXEC sp_xml_removedocument @rDoc

As you can see I've used a metaproperty because I want to get certain elements out of the XML and then send the entire file to the database.

So, getting to the point. The tempdata.form column is not receiving any data. All of the other columns are receiving the proper data. I haven't been able to find out why it's not send the XML, so I was wondering if you could offer any assistance.


Here is the script for the table, just in case you want it:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblTransaction_tempData]') and OBJECTPROPERTY(id,

N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTransaction] DROP CONSTRAINT FK_tblTransaction_tempData

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempData]') and OBJECTPROPERTY(id, N'IsUserTable') =

drop table [dbo].[tempData]

CREATE TABLE [dbo].[tempData] (
[Report_ID] [int] IDENTITY (100, 1) NOT NULL ,
[bintLong] [bigint] NULL ,
[bintLat] [bigint] NULL ,
[datReport] [datetime] NULL ,
[County_City_Code] [numeric](18, 0) NULL ,
[Make1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Owner_Name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Form] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cReviewed] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000