TRANSQL and XML help needed! - 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

Transact-SQL (2000)

TRANSQL and XML help needed!

Author

Topic

samj12
Starting Member

6 Posts

Posted - 2004-07-02 : 12:56:42

I am passing an XML document to a stored procedure in order for its attributes to be inserted into a table in the database.
I have TableA, with fields DocDate, CustID, CustName, and OrderTotal.

Fields CustID, and CustName will be retrieved from the XML document and DocDate, and OrderTotal are passed as inputs parameters.

I need to insert all 4 fields in one INSERT statement.

This is what i have right now:
DECLARE @idoc int
DECLARE @doc varchar(1000)
@doc = '<ROOT><customer CustID="5" CustName="Mike"></customer >
<customer CustID="6" CustName="James"></customer ></ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO tblTest
SELECT *
FROM OPENXML(@hDoc, '/ROOT/units')
WITH (SQFT DECIMAL,
Rooms INT)

EXEC sp_xml_removedocument @hDoc

My problem is how to fit the DocDate and the OrderTotal that were passed as input parameters into this SQL INSERT statement.

Any help would be greatly appreciated.

samj12
Starting Member

6 Posts

Posted - 2004-07-02 : 13:01:12

I just noticed that there was a mistake in my SQL above. This si the correction:

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO tblTest
SELECT *
FROM OPENXML(@idoc, '/ROOT/customer)
WITH (CustID INT,
CustName VARCHAR(200))

EXEC sp_xml_removedocument @idoc

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-02 : 14:04:36

[code]
declare @docdate datetime,@ordertotal int
select @docdate = getdate(), @ordertotal = 100

DECLARE @idoc int
DECLARE @doc varchar(1000)
set @doc = '<ROOT><customer CustID="5" CustName="Mike"></customer >
<customer CustID="6" CustName="James"></customer ></ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO tblTest (docdate,ordertotal,custid,custname)
SELECT @docdate,@ordertotal,*
FROM OPENXML(@idoc, '/ROOT/customer')
WITH (CustID INT,
CustName VARCHAR(200))[/code]

samj12
Starting Member

6 Posts

Posted - 2004-07-02 : 15:50:57

Thank you so much ehorn.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources