Как получить доступ к XML-узлу над курсором в хранимой процедуре?
Я пытаюсь импортировать xml-файл с помощью хранимой процедуры на сервер MSSQL 2012. Как вы можете видеть в xml-файле, он состоит из пары узлов, которые я хочу импортировать в таблицы:
production_orders - тогда как один XML-файл представлен одной записью в этой таблице
operations - production_order состоит из n операций
bom_items - операция состоит из n потребляемых предметов
У меня трудности со вставкой в bom_items Таблица. Я попробовал это с помощью следующей (части) процедуры. Моя проблема здесь в том, что я не могу получить доступ к operation_id - или, лучше сказать, - он остается пустым в базе данных. Можно ли получить доступ к узлу выше ConsumedItem таким образом или мой подход неверен?
/** INSERT FOR bom_items **/
DECLARE db_cursor CURSOR FOR
SELECT
bom_id = line.value('(ItemID/ID)[1]','nvarchar(255)'),
operation_id = line.value('(../ID)[1]','nchar(10)'),
stock_id = line.value('(Location/ID)[1]','nvarchar(255)'),
article_description = line.value('(Description)[1]','nvarchar(255)'),
line_number = line.value('(LineNumber)[1]','int'),
quantity = line.value('(Quantity)[1]','float')
FROM @xmlData.nodes('/DataArea/ProductionOrder/ProductionOrderDetail/BillOfResources/Operations/ConsumedItem')as A(line)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @bom_id, @operation_id, @stock_id, @article_description, @line_number, @quantity
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM bom_items WHERE production_order_id = @production_order_id AND line_number = @line_number AND operation_id = @operation_id)
UPDATE bom_items
SET bom_id = @bom_id,
stock_id = @stock_id,
article_description = @article_description,
quantity = @quantity
WHERE production_order_id = @production_order_id AND line_number = @line_number
ELSE
INSERT INTO bom_items (bom_id, production_order_id, stock_id, operation_id, article_description, line_number, quantity)
SELECT @bom_id, @production_order_id, @stock_id, @operation_id, @article_description, @line_number, @quantity
FETCH NEXT FROM db_cursor INTO @bom_id, @stock_id, @operation_id, @article_description, @line_number, @quantity
END
CLOSE db_cursor
DEALLOCATE db_cursor
XML-файл:
<DataArea>
<ProductionOrder>
<ProductionOrderHeader type="ShopFloor">
[...]
</ProductionOrderHeader>
<ProductionOrderDetail>
<Sequence>1</Sequence>
<Status>
<Code listID="Production Order Status">Released</Code>
<EffectiveDateTime>2013-11-13T10:45:51Z</EffectiveDateTime>
</Status>
<BillOfResources>
<Operations>
<ID>10</ID>
<NextID>20</NextID>
<Note/>
<Status>
<Code listID="Production Order Status">Planned</Code>
<EffectiveDateTime>2013-11-13T10:45:51Z</EffectiveDateTime>
</Status>
<ConsumedItem>
<ItemID>
<ID>BL_BLU</ID>
</ItemID>
<Description>Gehäuse blau</Description>
<LineNumber>10</LineNumber>
<Quantity unitCode="ST">1</Quantity>
<BaseUOMQuantity unitCode="ST">1</BaseUOMQuantity>
<Location type="Warehouse">
<ID>W_RM</ID>
</Location>
</ConsumedItem>
<ConsumedItem>
<ItemID>
<ID>BL_INC</ID>
</ItemID>
<Description>Glühlampe</Description>
<LineNumber>20</LineNumber>
<Quantity unitCode="ST">3</Quantity>
<BaseUOMQuantity unitCode="ST">3</BaseUOMQuantity>
<Location type="Warehouse">
<ID>W_RM</ID>
</Location>
</ConsumedItem>
</Operations>
<Operations>
<ID>20</ID>
<Note/>
<Status>
<Code listID="Production Order Status">Planned</Code>
<EffectiveDateTime>2013-11-13T10:45:51Z</EffectiveDateTime>
</Status>
<OutputItem>
<ItemID>
<ID>BIKELIGHT_RAC_BLU_INC_3_MET_I</ID>
</ItemID>
<Description>Bikelight Modell Racer</Description>
<Location type="Warehouse">
<ID>W_FWB</ID>
</Location>
</OutputItem>
</Operations>
</BillOfResources>
</ProductionOrderDetail>
</ProductionOrder>
</DataArea>
2 ответа
Проблема состояла во втором
FETCH NEXT FROM db_cursor INTO @bom_id, @stock_id, @operation_id, @article_description, @line_number, @quantity
тогда как порядок курсора: bom_id, operation_id, stock_id (...), поэтому stock_id был сопоставлен с operation_id и наоборот.
Спасибо всем читателям этого вопроса.
Прежде всего, чтобы избежать дублирования вашего кода и возможных ошибок, я предлагаю использовать структуру курсора, например:
declare db_cursor cursor for
select ...
open db_cursor
while 1 = 1
begin
fetch db_cursor into @variable
if @@fetch_status <> 0
break
...
end
close db_cursor
deallocate db_cursor
и во-вторых, зачем вам вообще нужен курсор? Вы можете использовать только один или два оператора, например, использовать merge для этой операции или update + insert, например так:
merge table1 as t
using (
select
a.b.value('key1[1]', 'int') as key1,
a.b.value('val1[1]', 'int') as val1,
a.b.value('val2[1]', 'int') as val2
from @xmlData.nodes('root/data') as a(b)
) as x on x.key1 = t.key1
when matched then
update set
val1 = x.val1,
val2 = x.val2
when not matched then
insert (key1, val1, val2)
values (x.key1, x.val1, x.val2);