Updating with OPENXML - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating with OPENXML

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-24 : 09:20:46

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)
as
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
(SELECT *
FROM OPENXML(@rDoc, '/formtransmit', 2)
WITH (FormID int 'form/CRASH_REPORT_NUM',
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
GO

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.

Thanks.

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
GO

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

1)
drop table [dbo].[tempData]
GO

CREATE TABLE [dbo].[tempData] (
[Report_ID] [int] IDENTITY (100, 1) NOT NULL ,
[CRASH_REPORT_NUM] [int] 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
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources