XML Parsing
-
I need to write a couple of stored procedures that will use OPENXML to parse thru a XML record to either update or insert information into 5 or 6 tables with one table being the primary key table for the other tables. For the insert stored procedure, I am assuming that I will include all fields in the XML format that need to be inserted into the table hierarchy. However for the update procedure, are there pros or cons from a design perspective for the update procedure, to only include the fields in the XML format that only need to be updated, not all of the fields from the table hierarchy? I would prefer to create the XML format only for the updated fields. How would one right the stored procedure to accomodate this?
Patrick Quinn
TRMS Database Administrator
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
-
I have created a sample query that includes an XML document. Note that for all of the new records you must specify all values for Not Null columns.
Russ
CREATE TABLE [OrderDetails] (
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[UnitPrice] [money] NOT NULL ,
[Quantity] [smallint] NOT NULL ,
[Discount] [real] NOT NULL
) ON [PRIMARY]
GO
insert into OrderDetails
values (10248, 11, 14.0, 12, .02)
insert into OrderDetails
values (10248, 42, 9.80, 10, .5)
insert into OrderDetails
values (10248, 72, 34.8, 5, .05)
go
select * from OrderDetails where OrderID = 10248
go
declare @xml int
declare @rt int
declare @OrderChecks nvarchar(4000)
-- OrderID, ProductID
-- Quantity, UnitPrice, Discount
set @OrderChecks = '<root>
<orderdetail OrderID="10248" ProductID="11" Quantity="30" UnitPrice="2.3" Discount=".1" />
<orderdetail OrderID="10248" ProductID="42" Quantity="35" UnitPrice="3.3" />
<orderdetail OrderID="10248" ProductID="72" Discount=".3" />
<orderdetail OrderID="10248" ProductID="12" Quantity="40" UnitPrice="3,3" Discount=".2" />
<orderdetail OrderID="10248" ProductID="13" Quantity="41" UnitPrice="3,4" Discount=".3" />
<orderdetail OrderID="10248" ProductID="14" Quantity="42" UnitPrice="3.5" Discount=".4" />
</root>'
exec @rt =sp_xml_preparedocument @xml output, @OrderChecks
if @rt = 0
begin
-- Insert records
insert into [OrderDetails]
(OrderID, ProductID, Quantity, UnitPrice, Discount)
Select OrderID, ProductID, Quantity, UnitPrice, Discount
from OpenXML(@xml, '/root/orderdetail', 1)
with (
[OrderID] [int] ,
[ProductID] [int] ,
[UnitPrice] [money] ,
[Quantity] [smallint] ,
[Discount] [real]
 
as awhere not exists (select * from [OrderDetails] b
where b.OrderID = a.OrderID
and b.ProductID = a.ProductID)
-- Update the records that have a proper id
UPDATE OrderDetails
SET
Quantity = isnull(b.Quantity, a.Quantity),
UnitPrice = isnull(b.UnitPrice, a.UnitPrice),
Discount = isnull(b.Discount, a.Discount)
from OrderDetails a
inner join
OpenXML(@xml, '/root/orderdetail', 1)
with
(
[OrderID] [int] ,
[ProductID] [int] ,
[UnitPrice] [money] ,
[Quantity] [smallint] ,
[Discount] [real]
 
as b
on a.OrderID = b.OrderID and a.ProductID = b.ProductID
exec sp_xml_removedocument @xml
set @xml = null
end
select * from OrderDetails where OrderID = 10248
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply