SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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)
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"
  Previous Topic Topic Next Topic  
 New 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.12 seconds. Powered By: Snitz Forums 2000