looping through xml document with OpenXML

Hi,
I am trying to compare records from an XML document to records in a table.
If the records in the XML document are not in the table then I wish to
insert those records.
How do I loop through the XML document within a store proc?
Has anyone done this who can provide a sample.
Thanks
Rather than looping, it'd probably be more efficient to use a NOT IN clause.
Have a look at the following example and see if you can adapt it for your
data:
USE Northwind
GO
DECLARE @.productsDoc nvarchar(2000)
SET @.productsDoc = '
<Products>
<Product ProductID="1">
<ProductName>Chai</ProductName>
<UnitPrice>10</UnitPrice>
</Product>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Products>'
DECLARE @.h integer
EXEC sp_xml_preparedocument @.h OUTPUT, @.productsDoc
SET IDENTITY_INSERT Products ON
INSERT Products (ProductID, ProductName, UnitPrice)
SELECT * FROM
OPENXML(@.h, 'Products/Product', 2)
WITH
(
ProductID integer '@.ProductID',
ProductName nvarchar(40),
UnitPrice money
)
WHERE ProductID NOT IN
(SELECT ProductID FROM Products)
SET IDENTITY_INSERT Products OFF
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:8CF49B69-9E84-4B7D-96AE-C2E0FCE854E2@.microsoft.com...
Hi,
I am trying to compare records from an XML document to records in a table.
If the records in the XML document are not in the table then I wish to
insert those records.
How do I loop through the XML document within a store proc?
Has anyone done this who can provide a sample.
Thanks
|||Thanks Graeme.
Looks like your code should work and I will give it a try.
"Graeme Malcolm" wrote:

> Rather than looping, it'd probably be more efficient to use a NOT IN clause.
> Have a look at the following example and see if you can adapt it for your
> data:
> USE Northwind
> GO
> DECLARE @.productsDoc nvarchar(2000)
> SET @.productsDoc = '
> <Products>
> <Product ProductID="1">
> <ProductName>Chai</ProductName>
> <UnitPrice>10</UnitPrice>
> </Product>
> <Product ProductID="101">
> <ProductName>Porridge</ProductName>
> <UnitPrice>16</UnitPrice>
> </Product>
> <Product ProductID="102">
> <ProductName>Haggis</ProductName>
> <UnitPrice>19</UnitPrice>
> </Product>
> </Products>'
> DECLARE @.h integer
> EXEC sp_xml_preparedocument @.h OUTPUT, @.productsDoc
> SET IDENTITY_INSERT Products ON
> INSERT Products (ProductID, ProductName, UnitPrice)
> SELECT * FROM
> OPENXML(@.h, 'Products/Product', 2)
> WITH
> (
> ProductID integer '@.ProductID',
> ProductName nvarchar(40),
> UnitPrice money
> )
> WHERE ProductID NOT IN
> (SELECT ProductID FROM Products)
> SET IDENTITY_INSERT Products OFF
> Hope that helps,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:8CF49B69-9E84-4B7D-96AE-C2E0FCE854E2@.microsoft.com...
> Hi,
> I am trying to compare records from an XML document to records in a table.
> If the records in the XML document are not in the table then I wish to
> insert those records.
> How do I loop through the XML document within a store proc?
> Has anyone done this who can provide a sample.
> Thanks
>
>