January 16, 2005 at 7:19 pm
Hi,
Ok I am importing an XML document via a stored procedure, works a treat. I am new to stored prcedures so excuse me if the question is stupid.
Below is my current stored procedure, which takes an XML document, and adds all the data to my database, in 2 fields.
CREATE PROCEDURE dbo.report_event @doc ntext
AS
SET NOCOUNT ON
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
BEGIN TRANSACTION
INSERT dbo.event_data
SELECT
attribute_type,
attribute_value
FROM OPENXML(@idoc, '/Event/*')
WITH ( attribute_type varchar(64) '@mp:localname',
attribute_value nvarchar(1024) 'child::text()'
)
COMMIT TRANSACTION
EXEC sp_xml_removedocument @idoc
What I want to do is rewrite this bit:
WITH ( attribute_type varchar(64) '@mp:localname',
attribute_value nvarchar(1024) 'child::text()'
)
To add the 'child::text()' value (the value of @mp:localname), to the table, instead of to a field called attribute_value, to the name of the node '@mp:localname'
So when adding, I am writing the value of the node: 'child::text()'
to the name of the node '@mp:localname' (which I have setup in my database)
Any help is apprecaited.
January 18, 2005 at 12:42 am
I am failing to understand what sort of help you require.What exactly do you want to do?
I am uploading an xml file and this is how i am doing it
ALTER PROC cp_insOrderDetails
@BatchNo bigint,
@doc ntext
AS
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
BEGIN TRAN
INSERT INTO OrderDetails
(BatchNo,OrderNo,Product,Quantity,Price)
SELECT @BatchNo,OrderNo,Product,Quantity,Price
FROM OPENXML (@idoc, '/NewDataSet/Table',2)
WITH (OrderNo bigint,
Product integer,
Quantity integer
Price Money)
COMMIT
EXEC sp_xml_removedocument @idoc
January 18, 2005 at 12:47 am
January 18, 2005 at 4:36 pm
Hi,
I dont always know what fields will be in the XML file, so I want to loop through it programatically, which I can do, but cant translate the name of the field to be the field I update in the database, I can do it as you have done, but I can guarantee that all those fields will appear in the XML file
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply