Хранимая процедура для вставки / обновления базы данных из XML

Question

Хранимая процедура для вставки / обновления базы данных из XML

У меня есть хранимая процедура в SQL Server 2012, которая берет данные из файла XML и вставляет / обновляет их в таблицу базы данных.

Хранимая процедура:

ALTER PROCEDURE [dbo].[ProcItem]
   (@cItemID nvarchar(50) = NULL,
    @XMLdata XML)
AS
BEGIN
DECLARE
    item_cursor cursor for 
    SELECT TempTable.i.value('ItemID[1]','nvarchar(50)')
    FROM @XMLdata.nodes('/Garage/WareHouse/ItemGroup/Item')AS TempTable(i)
open item_cursor;
fetch next from item_cursor into @cItemID
while @@FETCH_STATUS=0
BEGIN
IF EXISTS (SELECT ItemID FROM WareHouse WHERE ItemID = @cItemID)
    BEGIN
    UPDATE WareHouse
    SET 
    ItemGroupID = TempTable.a.value('ItemGroupID[1]','nvarchar(50)'),
    Color = TempTable.a.value('Color[1]','nvarchar(50)'),
    Location = TempTable.a.value('Location[1]','nvarchar(50)'),
    Price = TempTable.a.value('Price[1]','nvarchar(50)'),
    Manufacture = TempTable.a.value('Manufacture[1]','nvarchar(100)'),
    Supplier = TempTable.a.value('Supplier[1]','nvarchar(100)'),
    SerialNumber = TempTable.a.value('SerialNumber[1]','nvarchar(100)')
    FROM
    @XMLdata.nodes('/Garage/WareHouse/ItemGroup/Item')AS TempTable(a)
    WHERE  ItemID = TempTable.a.value('ItemID[1]','nvarchar(100)')
    END

ELSE 
    BEGIN
    INSERT INTO WareHouse (ItemID, ItemGoupID, Color, Location, Price, Manufacture, Supplier, SerialNumber)

    SELECT
    ItemID = TempTable.b.value('ItemID[1]','nvarchar(50)'),
    ItemGroupID = TempTable.b.value('ItemGroupID[1]','nvarchar(50)'),
    Color = TempTable.b.value('Color[1]','nvarchar(50)'),
    Location = TempTable.b.value('Location[1]','nvarchar(50)'),
    Price = TempTable.b.value('Price[1]','nvarchar(50)'),
    Manufacture = TempTable.b.value('Manufacture[1]','nvarchar(100)'),
    Supplier = TempTable.b.value('Supplier[1]','nvarchar(100)'),
    SerialNumber = TempTable.b.value('SerialNumber[1]','nvarchar(100)')
    FROM
    @XMLdata.nodes('/Garage/WareHouse/ItemGroup/Item')AS TempTable(b)
    END
fetch next from item_cursor into @cItemID
END
close item_cursor
deallocate item_cursor
END

И мой файл XML:

<?xml version="1.0" encoding="UTF-8"?>
<Garage>
<WareHouse>
<WareHouseID>195</HallID>
<Name>CC Cuts</Name>
<Location>Boston</Location>
<ItemGroup>
<WareHouseID>195</WareHouseID>
<ItemGroupID>235</ItemGroupID>
<Name>C Parts</Name>
<Item>
<ItemID>645</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570683</SerialNumber>
</Item>
<Item>
<ItemID>646</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570684</SerialNumber>
</Item>
...
</ItemGroup>
</WareHouse>
</Garage>

Код работает, но если я пытаюсь добавить XML-файл большего размера, это занимает огромное время. Я верю, что это потому, что я использую курсор. Есть ли еще более эффективный способ вставки / обновления данных из XML-файла? И, может быть, кто-то может дать мне пример.

0

sql-server xml stored-procedures sql-server-2012 database-cursor

Источник

user3637956 27 май '14 в 21:02

1 ответ

Решение

Другие вопросы по тегам sql-server xml stored-procedures sql-server-2012 database-cursor

user214977 27 май '14 в 21:07 2014-05-27 21:07 · Accepted Answer · 2014-05-27 21:07

Создайте таблицу @variable или #temp.
"Измельчите" xml в таблицу @variable или #temp.
Обновление / вставка из таблицы @variable или #temp.

Что-то вроде этого:

declare @XMLdata xml
select @XMLdata = 
'
<Garage>
<WareHouse>
<WareHouseID>195</WareHouseID>
<Name>CC Cuts</Name>
<Location>Boston</Location>
<ItemGroup>
<WareHouseID>195</WareHouseID>
<ItemGroupID>235</ItemGroupID>
<Name>C Parts</Name>
<Item>
<ItemID>645</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570683</SerialNumber>
</Item>
<Item>
<ItemID>646</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570684</SerialNumber>
</Item>
</ItemGroup>
</WareHouse>
</Garage>
'




    IF OBJECT_ID('tempdb..#HolderOne') IS NOT NULL
    begin
            drop table #HolderOne
    end


    CREATE TABLE #HolderOne
    ( 
    SurrogateKeyIDENTITY int not null IDENTITY (1,1)
    ,  ItemGroupID nvarchar(50)
    , Color nvarchar(50)
    , Location nvarchar(50)
    , Price nvarchar(50)
    , Manufacture nvarchar(100)
    , Supplier nvarchar(100)
    , SerialNumber nvarchar(100)
    )

    INSERT INTO #HolderOne ( 
          ItemGroupID
        , Color
        , Location
        , Price
        , Manufacture
        , Supplier
        , SerialNumber
    )

    Select 
     xmlAlias.a.value('ItemGroupID[1]','nvarchar(50)'),
        Color = xmlAlias.a.value('Color[1]','nvarchar(50)'),
        Location = xmlAlias.a.value('Location[1]','nvarchar(50)'),
        Price = xmlAlias.a.value('Price[1]','nvarchar(50)'),
        Manufacture = xmlAlias.a.value('Manufacture[1]','nvarchar(100)'),
        Supplier = xmlAlias.a.value('Supplier[1]','nvarchar(100)'),
        SerialNumber = xmlAlias.a.value('SerialNumber[1]','nvarchar(100)')
        FROM
        @XMLdata.nodes('/Garage/WareHouse/ItemGroup/Item')AS xmlAlias(a)


    Select * from #HolderOne

    INSERT INTO WareHouse (ItemID, ItemGroupID, Color, Location, Price, Manufacture, Supplier, SerialNumber)
    Select
        0
        ,  ItemGroupID
        , Color
        , Location
        , Price
        , Manufacture
        , Supplier
        , SerialNumber  
    from #HolderOne holder
        where not exists ( select null from dbo.WareHouse innerRealTable where innerRealTable.ItemGroupID = holder.ItemGroupID )

    Update WareHouse
    Set 
            ItemGroupID = holder.ItemGroupID
        , Color = holder.Color
        , Location = holder.Location
        , Price = holder.Price
        , Manufacture = holder.Manufacture
        , Supplier = holder.Supplier
        , SerialNumber = holder.SerialNumber
    from #HolderOne holder , dbo.WareHouse  ware
    Where
        holder.ItemGroupID = ware.ItemGroupID




    IF OBJECT_ID('tempdb..#HolderOne') IS NOT NULL
    begin
            drop table #HolderOne
    end

Вот немного улучшенный код @variableTable...

declare @XMLdata xml
select @XMLdata = 
'
<Garage>
<WareHouse>
<WareHouseID>195</WareHouseID>
<Name>CC Cuts</Name>
<Location>Boston</Location>
<ItemGroup>
<WareHouseID>195</WareHouseID>
<ItemGroupID>235</ItemGroupID>
<Name>C Parts</Name>
<Item>
<ItemID>645</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570683</SerialNumber>
</Item>
<Item>
<ItemID>646</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570684</SerialNumber>
</Item>
</ItemGroup>
</WareHouse>
</Garage>
'



    /* changed to use @variable table
    IF OBJECT_ID('tempdb..#HolderOne') IS NOT NULL
    begin
            drop table #HolderOne
    end
    */


    /*CREATE TABLE #HolderOne*/
    Declare @HolderOne Table
    ( 
    SurrogateKeyIDENTITY int not null IDENTITY (1,1)
    , ItemID int
    , ItemGroupID int
    , Color nvarchar(50)
    , Location nvarchar(50)
    , Price nvarchar(50)
    , Manufacture nvarchar(100)
    , Supplier nvarchar(100)
    , SerialNumber nvarchar(100)
    )

    INSERT INTO @HolderOne ( 
          ItemID
        , ItemGroupID
        , Color
        , Location
        , Price
        , Manufacture
        , Supplier
        , SerialNumber
    )

    Select 
        xmlAlias.a.value('ItemID[1]','int'),
        xmlAlias.a.value('ItemGroupID[1]','int'),
        xmlAlias.a.value('Color[1]','nvarchar(50)'),
        xmlAlias.a.value('Location[1]','nvarchar(50)'),
        xmlAlias.a.value('Price[1]','nvarchar(50)'),
        xmlAlias.a.value('Manufacture[1]','nvarchar(100)'),
        xmlAlias.a.value('Supplier[1]','nvarchar(100)'),
        xmlAlias.a.value('SerialNumber[1]','nvarchar(100)')
        FROM
        @XMLdata.nodes('/Garage/WareHouse/ItemGroup/Item')AS xmlAlias(a)


    Select * from @HolderOne


    INSERT INTO WareHouse (/* ItemID, */ ItemGroupID, Color, Location, Price, Manufacture, Supplier, SerialNumber)
    Select
          /*holder.ItemId  , */
          holder.ItemGroupID
        , holder.Color
        , holder.Location
        , holder.Price
        , holder.Manufacture
        , holder.Supplier
        , holder.SerialNumber  
    from @HolderOne holder
        where not exists ( select null from dbo.WareHouse innerRealTable where innerRealTable.ItemId = holder.ItemId )

    Update WareHouse
    Set 
          ItemGroupID = holder.ItemGroupID
        , Color = holder.Color
        , Location = holder.Location
        , Price = holder.Price
        , Manufacture = holder.Manufacture
        , Supplier = holder.Supplier
        , SerialNumber = holder.SerialNumber
    from @HolderOne holder , dbo.WareHouse  ware
    Where
        holder.ItemId = ware.ItemId



        /* Changed to use variable table
    IF OBJECT_ID('tempdb..#HolderOne') IS NOT NULL
    begin
            drop table #HolderOne
    end
    */