How to add a new line if there is a specific text appears in XML data?

Question

I'm passing an XML to a stored procedure for inserting. XML contains some pieces of information like product specification, which is a string.

Here is a sample how the XML looks like:

<?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>

In this product specification section, there is Purpose section added. So, I want to insert it as a new line or display it as a new line and I want to do this using T-SQL

here is the insertion code of XML to a temp table

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

Answer 1

Try this to find how to extract the data nested within your 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);

My approach assumes, that there might be several <item> elements within <Details>.

Just some explanation: The <item> element is a self-closing element with all data placed within attributes. This is a very easy form to query. Good for you...

Btw: It would be best to avoid the <?xml blah?>-declaration at all. Within SQL-Server this declaration is useless and can disturb with encodings...

UPDATE

An enhanced query to parse the spec in lines and extract the 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);

Answer 2

Here are the steps to solve this problem.

1. First get whole string from ProductSpec XML tag as column name "ProductSpec".

2. Get the sub-string from ProductSpec where sub string started from Purpose in new column as "ProductSpecPurpose".

3. Append char(10) or char(13) as per your need in string which you have extracted. E.g. char(10) + ProductSpecPurpose.

4. Merge the two columns which created in step 1 & 2.

5. Save it.

PS: I did not write solution directly so that at least you can try different sql functions and learn more. Because I believe in learning by ourselves rather spoon feeding. Give it try and if you are not able to figure it out. Do comment I will then write whole sql answer.