Listing 4: OpenXML Example That Inserts into Multiple Tables from a Single XML Document

DECLARE @h int
DECLARE @xmldoc varchar(1000)

set @xmldoc =
'<root>
<stores stor_id="8888"
        stor_name="Bob''s Books"
        stor_address="111 Somewhere"
        city="Portland"
        state="OR"
        zip="97225">
<discounts discounttype="Big Discount"
           stor_id="8888"
           discount="00.00" />
</stores>
</root>'

EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc

INSERT INTO stores
SELECT *
FROM OpenXML(@h,'/root/stores')
WITH stores

INSERT INTO discounts
SELECT *
FROM OpenXML(@h,'/root/stores/discounts', 0)
WITH discounts

EXEC sp_xml_removedocument @h