Load XML file to SQL Server 2008 R2 temp table 1

[thisisboni]

Hi:

we need to get an xml file from an sftp location and load it to a table.
below is a sample xml file

<?xml version="1.0" encoding="UTF-8"?>
<Inventory xmlns="

http://xyz.com">

<Stock WarehouseId="0" SkuId="787935679937"/>
<Stock WarehouseId="0" SkuId="787935679944"/>
<Stock WarehouseId="2" SkuId="787935938508" QuantityAvailable="0"/>
<Stock WarehouseId="2" SkuId="787935938515" QuantityAvailable="0"/>
</Inventory>

what is the 'best' way to do this - avg file size ~5Mb

Thanks

 

[gmmastros]

I have never done this, but I would suggest you take a good look at SQL Server Integration Server (SSIS) for getting the file from sftp location.

Once you have the data, you can parse it like this:

Declare @XML VarChar(max)
Declare @iDoc Int

Set @XML ='
<?xml version="1.0" encoding="UTF-8"?>
<Inventory xmlns="[URL unfurl="true"]http://xyz.com">[/URL]
    <Stock WarehouseId="0" SkuId="787935679937"/>
    <Stock WarehouseId="0" SkuId="787935679944"/>
    <Stock WarehouseId="2" SkuId="787935938508" QuantityAvailable="0"/>
    <Stock WarehouseId="2" SkuId="787935938515" QuantityAvailable="0"/>
</Inventory>'

exec sp_xml_preparedocument @iDoc OUTPUT, @XML, '<Inventory xmlns:tag="[URL unfurl="true"]http://xyz.com"/>'[/URL]

Select  *
From    OpenXML(@iDoc, '/tag:Inventory/tag:Stock', 3)
With    (
        WarehouseId       Int         '@WarehouseId',
        SkuId             VarChar(20) '@SkuId',
        QuantityAvailable Int         '@QuantityAvailable'
        )

exec sp_xml_removedocument @iDoc

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thisisboni]

Thanks - it is an express edition so I don't think SSIS is included but the OPENXML works fine

Thanks !