> How would you select from an XML document in sql server?
We use MS SQL Server 2000. Here is an example I just ripped out of my
code and pasted in. (Sorry it is kind of long-- didn't have time to trim
it down)
Pass this xml string into the proc and the table variable is filled with
a record corresponding to each element in the XML. Then a single insert
is done.
<ROOT><bank_account_transaction
bank_account_transaction_id="00b42916-c5f5-4bc5-9ca2-59d6c7e56a19"
bank_account_id="9ee2b725-8122-4cdf-8b1b-f512b6a5e1d2"
bank_account_sub_id="4bf56758-459e-4c0e-a148-99f9915716ba"
full_order_number="0041351NLS05"
order_id="4cd3d8c4-1f4f-4d78-aa60-dbc6bac4b2d5"/><bank_account_transacti
on bank_account_transaction_id="8422f725-1eab-4139-8052-75b1191ff5e0"
bank_account_id="9ee2b725-8122-4cdf-8b1b-f512b6a5e1d2"
bank_account_sub_id="4bf56758-459e-4c0e-a148-99f9915716ba"
full_order_number="0041351NLS05"
order_id="4cd3d8c4-1f4f-4d78-aa60-dbc6bac4b2d5"/><bank_account_transacti
on bank_account_transaction_id="31770dc3-fc98-453a-b68c-16a9677c7108"
bank_account_id="9ee2b725-8122-4cdf-8b1b-f512b6a5e1d2"
bank_account_sub_id="4bf56758-459e-4c0e-a148-99f9915716ba"
full_order_number="0041545NLS05"
order_id="af911a99-c460-49a3-ace8-fd429c68e552"/></ROOT>
CREATE PROCEDURE dbo.p_bank_account_transaction_mass_update
@output_message AS varchar(255) OUTPUT,
@executer_entity_id AS uniqueidentifier,
@executer_entity_hierarchy_id AS uniqueidentifier,
@executer_company_id AS uniqueidentifier,
@xml_transaction_list AS text,
@u_bank_account_transaction_status_type AS uniqueidentifier
AS
SET NOCOUNT ON
DECLARE @rtn AS int
DECLARE @xmlDoc integer
DECLARE @tran_count AS int
DECLARE @getDate AS datetime
SET @getDate = getDate()
SET @tran_count = @@trancount
DECLARE @tmp_transaction_list TABLE
( bank_account_transaction_id uniqueidentifier PRIMARY KEY,
new_bank_account_transaction_id uniqueidentifier DEFAULT
newID(),
bank_account_id uniqueidentifier,
bank_account_sub_id uniqueidentifier,
order_id uniqueidentifier,
change_reason varchar(500),
u_id_change_reason uniqueidentifier,
u_bank_account_transaction_status_type uniqueidentifier)
------------------------------------------------------------------------
-------------
-- IF TRANASCTIONS PASSED VIA XML
------------------------------------------------------------------------
-------------
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @xml_transaction_list
INSERT INTO @tmp_transaction_list
(bank_account_transaction_id,
bank_account_id,
bank_account_sub_id,
order_id,
change_reason,
u_id_change_reason)
SELECT bank_account_transaction_id,
bank_account_id,
bank_account_sub_id,
order_id,
change_reason,
u_id_change_reason
FROM OPENXML(@xmlDoc, N'/ROOT/bank_account_transaction')
WITH (bank_account_transaction_id uniqueidentifier
'@bank_account_transaction_id',
bank_account_id uniqueidentifier '@bank_account_id',
bank_account_sub_id uniqueidentifier '@bank_account_sub_id',
order_id uniqueidentifier '@order_id',
change_reason varchar(500) '@change_reason',
u_id_change_reason uniqueidentifier '@u_id_change_reason')
EXEC sp_xml_removedocument @xmlDoc
BEGIN TRANSACTION
INSERT INTO bank_account_transaction_status (
bank_account_transaction_status_id,
bank_account_transaction_id,
u_bank_account_transaction_status_type,
u_id_change_reason,
change_reason,
datetime_modified,
entity_id_modified )
SELECT newid(),
tmp.bank_account_transaction_id,
@u_bank_account_transaction_status_type,
tmp.u_id_change_reason,
tmp.change_reason,
@getDate,
@executer_entity_id
FROM @tmp_transaction_list tmp
SET @rtn = @@ERROR
IF @rtn <> 0
BEGIN
IF @tran_count = 0 ROLLBACK TRANSACTION
SET @output_message = 'ERROR ' +CAST(@rtn AS varchar(15))+ '
OCCURRED WHILE INSERTING BANK_ACCOUNT_TRANSACTION_STATUS RECORD '
RETURN @rtn
END
IF @tran_count = 0 COMMIT TRANSACTION
SET @output_message = 'MODIFY SUCCESSFUL '
RETURN 0
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233036
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
