Вставить новую строку на основе конкретной строки находится в XML с использованием SQL Server?

Я передаю XML в хранимую процедуру для вставки. XML содержит некоторую информацию, например, спецификацию продукта, которая является строкой.

Вот пример того, как выглядит XML:

<?xml version="1.0"?>
<Details>
    <item Unit="PilotApp.DataAccessObject.DTO.Unit" 
          PSASysCommon="" 
          ProductModel="PilotApp.DataAccessObject.DTO.ProductModel" 
          Product="PilotSmithApp.DataAccessObject.DTO.Product" 
          SpecTag="62793f05-25ab-41b5-a081-f6c542f1f7cd" 
          Rate="100" UnitCode="1" Qty="1" 
          ProductSpec="Pilot Cone Blender Model No. Pilot PCB - 10 , volume of vessel -30 Ltr , handling capacity per batch by weight - 10 Kg and by volume - 20 Ltr. with motor - 0.25 HP/3 ph. Crompton make or equivalent , feeding door , discharge butterfly valve and safety guard .Material of construction of contact stainless steel (AISI) 304 and frame in carbon steel . Purpose : For blending dry powder and granules" 
          ProductModelID="10c0b51b-7799-4597-a4af-7c3fd431353b" 
          ProductID="15745d53-8219-431e-a0e3-0d319abf132d" 
          EnquiryID="00f9436c-ed2a-442c-b333-16348b0d8c33" 
          ID="e6812788-e67e-4874-bf80-87b39579a837"/>
</Details>

В этом разделе спецификации продукта добавлен раздел Назначение. Итак, я хочу вставить его как новую строку или отобразить как новую строку, и я хочу сделать это с помощью T-SQL

вот код вставки XML во временную таблицу

DECLARE @temp TABLE(
ID UNIQUEIDENTIFIER,
EnquiryID UNIQUEIDENTIFIER,
ProductID UNIQUEIDENTIFIER,
ProductModelID UNIQUEIDENTIFIER,
ProductSpec NVARCHAR(MAX),
Qty DECIMAL(18,2),
Rate DECIMAL(18,2),
UnitCode INT,
SpecTag UNIQUEIDENTIFIER,
IsProcessed bit,
tmpID UNIQUEIDENTIFIER
);
------------parse from xml to temptable ----


        INSERT INTO @temp(ID,EnquiryID,ProductID,ProductModelID,
        ProductSpec,Qty,Rate,UnitCode,SpecTag,IsProcessed,tmpID)
        SELECT T.ID,T.EnquiryID,T.ProductID,T.ProductModelID,replace(replace(replace(replace(T.ProductSpec,'&quot;','"'),'&amp;','&'),'&lt;','<'),'&gt;','>') AS ProductSpec,
        T.Qty,T.Rate,T.UnitCode,
        -----modified on 14-May-2018 added field SpecTag in EnquiryDetail by Thomson
        CASE WHEN T.SpecTag=CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER) THEN NEWID() ELSE T.SpecTag END,
        T.IsProcessed,T.tmpID FROM
        (SELECT [xmlData].[Col].value('./@ID', 'UNIQUEIDENTIFIER') as ID,
        [xmlData].[Col].value('./@EnquiryID', 'UNIQUEIDENTIFIER') as EnquiryID,
        [xmlData].[Col].value('./@ProductID', 'UNIQUEIDENTIFIER') as ProductID,
        [xmlData].[Col].value('./@ProductModelID', 'UNIQUEIDENTIFIER') as ProductModelID,
        [xmlData].[Col].value('./@ProductSpec', 'NVARCHAR(MAX)') as ProductSpec,
        [xmlData].[Col].value('./@Qty','DECIMAL(18,2)')as Qty,
        [xmlData].[Col].value('./@Rate','DECIMAL(18,2)')as Rate,
        [xmlData].[Col].value('./@UnitCode','INT')as UnitCode,
        [xmlData].[col].value('./@SpecTag','UNIQUEIDENTIFIER') AS SpecTag,
        0 as IsProcessed,
        newid() as tmpID
        from @DetailXML.nodes('/Details/item') as [xmlData]([Col])) T
Источник

2 ответа

Попробуйте это, чтобы узнать, как извлечь данные, вложенные в ваш XML:

DECLARE @xml XML=
'<?xml version="1.0"?>
<Details>
    <item Unit="PilotApp.DataAccessObject.DTO.Unit" 
          PSASysCommon="" 
          ProductModel="PilotApp.DataAccessObject.DTO.ProductModel" 
          Product="PilotSmithApp.DataAccessObject.DTO.Product" 
          SpecTag="62793f05-25ab-41b5-a081-f6c542f1f7cd" 
          Rate="100" UnitCode="1" Qty="1" 
          ProductSpec="Pilot Cone Blender Model No. Pilot PCB - 10 , volume of vessel -30 Ltr , handling capacity per batch by weight - 10 Kg and by volume - 20 Ltr. with motor - 0.25 HP/3 ph. Crompton make or equivalent , feeding door , discharge butterfly valve and safety guard .Material of construction of contact stainless steel (AISI) 304 and frame in carbon steel . Purpose : For blending dry powder and granules" 
          ProductModelID="10c0b51b-7799-4597-a4af-7c3fd431353b" 
          ProductID="15745d53-8219-431e-a0e3-0d319abf132d" 
          EnquiryID="00f9436c-ed2a-442c-b333-16348b0d8c33" 
          ID="e6812788-e67e-4874-bf80-87b39579a837"/>
</Details>';

SELECT itm.value('@Unit','nvarchar(max)') AS Unit
      ,itm.value('@PSASysCommon','nvarchar(max)') AS PSASysCommon
      ,itm.value('@Product','nvarchar(max)') AS Product
      ,itm.value('@SpecTag','uniqueidentifier') AS SpecTag
      ,itm.value('@Rate','int') AS Rate
      ,itm.value('@UnitCode','int') AS UnitCode
      ,itm.value('@Qty','int') AS Qty
      ,itm.value('@ProductSpec','nvarchar(max)') AS ProductSpec
      ,itm.value('@ProductModelID','uniqueidentifier') AS ProductModelID
      ,itm.value('@ProductID','uniqueidentifier') AS ProductID
      ,itm.value('@ID','uniqueidentifier') AS ID
FROM @xml.nodes('/Details/item') A(itm);

Мой подход предполагает, что может быть несколько <item> элементы внутри <Details>,

Просто какое-то объяснение: <item> Элемент является самозакрывающимся элементом со всеми данными, помещенными в атрибуты. Это очень простая форма для запроса. Повезло тебе...

Кстати: было бы лучше избежать <?xml blah?>-объявление вообще. В SQL-сервере это объявление бесполезно и может мешать с кодировками...

ОБНОВИТЬ

Расширенный запрос для разбора спецификации в строках и извлечения Purpose:

SELECT itm.value('@Unit','nvarchar(max)') AS Unit
      ,itm.value('@PSASysCommon','nvarchar(max)') AS PSASysCommon
      ,itm.value('@Product','nvarchar(max)') AS Product
      ,itm.value('@SpecTag','uniqueidentifier') AS SpecTag
      ,itm.value('@Rate','int') AS Rate
      ,itm.value('@UnitCode','int') AS UnitCode
      ,itm.value('@Qty','int') AS Qty
      ,itm.value('@ProductSpec','nvarchar(max)') AS ProductSpec
      ,itm.value('@ProductModelID','uniqueidentifier') AS ProductModelID
      ,itm.value('@ProductID','uniqueidentifier') AS ProductID
      ,itm.value('@ID','uniqueidentifier') AS ID
      ,LTRIM(RTRIM(ProductSpecLine.value('text()[1]','nvarchar(max)'))) AS ProductSpecLine_Text
      ,Purpose
FROM @xml.nodes('/Details/item') A(itm)
OUTER APPLY(SELECT CAST('<x>' + REPLACE((SELECT itm.value('@ProductSpec','nvarchar(max)') AS [*] FOR XML PATH('')),',','</x><x>') + '</x>' AS XML)) B(x)
OUTER APPLY B.x.nodes('/x') C(ProductSpecLine)
OUTER APPLY (SELECT CASE WHEN CHARINDEX('Purpose : ',ProductSpecLine.value('text()[1]','nvarchar(max)'))>0 
                         THEN SUBSTRING(ProductSpecLine.value('text()[1]','nvarchar(max)'),CHARINDEX('Purpose : ',ProductSpecLine.value('text()[1]','nvarchar(max)')),1000)
                    END) D(Purpose);
Источник

Вот шаги для решения этой проблемы.

  1. Сначала получите целую строку из XML-тега ProductSpec в качестве имени столбца "ProductSpec".

  2. Получите подстроку из ProductSpec, где подстрока началась с Purpose в новом столбце как "ProductSpecPurpose".

  3. Добавьте char(10) или char(13) в соответствии с вашими потребностями в строке, которую вы извлекли. Например, char(10) + ProductSpecPurpose.

  4. Объедините два столбца, которые были созданы на шаге 1 и 2.

  5. Сохрани это.

PS: я не писал решения напрямую, чтобы вы могли попробовать разные функции sql и узнать больше. Потому что я верю в обучение самостоятельно, а не кормление с ложечки. Дайте ему попробовать, и если вы не можете понять это. Оставьте комментарий, я напишу весь ответ sql.

Источник
Другие вопросы по тегам