Help to create column and data from Long text

Question

Hi, I have to create columns and data from 'OperationDetail' column of #Longdata table. I have to get the column which is inside of <> and data is Just after<>

Example-1: <useridNo>3929 -- Here useridNo is a column's name and 3929 is the data of useridNo
Example-2: <username>Rob -- Here username is a column's name and Rob is the data of username

Create table #Longdata ([Date1] [datetime], OperationDetail [text] NULL)
Insert into #Longdata values ('2019-10-11','<Name>AAA</Tred><useridNo>3929</NewUserid><username>Rob</newusername><EnvCode>BB_HRD<ReportId>10865</ReportIdN><LogCode>-10</Logidfile></Gter>'

Desired Output:
Date1 Name useridNo username EnvCode LogCode
2019-10-11 AAA 3929 Rob BB_HRD -10

Answer 1

Hi @Mike ,

Please also refer below method and check whether it is working.

Step 1: create one function SplitString.

CREATE FUNCTION [dbo].[SplitString]  
(  
    @List NVARCHAR(MAX),  
    @Delim VARCHAR(255)  
)  
RETURNS TABLE  
AS  
    RETURN ( SELECT [Value] FROM   
      (   
        SELECT   
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
          FROM sys.all_objects) AS x  
          WHERE Number <= LEN(@List)  
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
      ) AS y  
    );  

Step 2:

;with cte as (  
select cast(date1 as date) date1, [value]    
from #Longdata  
cross apply dbo.[SplitString](OperationDetail,'<')  
)  
,cte2 as (  
select date1,case when CHARINDEX('>',value)>0 then left(value,CHARINDEX('>',value)-1) end  val1  
,case when CHARINDEX('>',value)>0 then SUBSTRING(value,CHARINDEX('>',value)+1,LEN(value)-CHARINDEX('>',value)) end val2   
from cte )  
select * from   
(select * from cte2) a   
pivot   
(max(val2) for val1 in (Name,useridNo,username,EnvCode,LogCode)) b  

Output:

date1	Name	useridNo	username	EnvCode	LogCode  
2019-10-11	AAA	3929	Rob	BB_HRD	-10  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Mike ,

Please try the following 2 step solution.

1. Convert TEXT data type mumbo-jumbo into a well-formed XML.
2. Query XML and return a rectangular format by using XQuery.

SQL

-- DDL and sample data population, start  
DECLARE @tbl table (Date1 DATETIME, OperationDetail [text] NULL);  
INSERT INTO @tbl (Date1, OperationDetail)  
VALUES  
('2019-10-11','<Name>AAA</Tred><useridNo>3929</NewUserid><username>Rob</newusername><EnvCode>BB_HRD<ReportId>10865</ReportIdN><LogCode>-10</Logidfile></Gter>');  
-- DDL and sample data population, end  
  
;WITH rs AS  
(  
 SELECT Date1  
 , CAST('<Gter>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(OperationDetail AS VARCHAR(max))  
 , 'Tred','Name')  
 , 'NewUserid','useridNo')  
 , 'newusername', 'username')  
 , '<ReportId>', '</EnvCode><ReportId>')  
 , 'ReportIdN', 'ReportId')  
 , 'Logidfile', 'LogCode') AS XML) AS x  
 FROM @tbl  
)  
SELECT Date1  
 , x.value('(/Gter/Name/text())[1]','VARCHAR(30)') AS [Name]  
 , x.value('(/Gter/useridNo/text())[1]','VARCHAR(30)') AS useridNo  
 , x.value('(/Gter/username/text())[1]','VARCHAR(30)') AS username  
 , x.value('(/Gter/EnvCode/text())[1]','VARCHAR(30)') AS EnvCode  
 , x.value('(/Gter/LogCode/text())[1]','VARCHAR(30)') AS LogCode  
FROM rs;  

Output

+-------------------------+------+----------+----------+---------+---------+  
|          Date1          | Name | useridNo | username | EnvCode | LogCode |  
+-------------------------+------+----------+----------+---------+---------+  
| 2019-10-11 00:00:00.000 | AAA  |     3929 | Rob      | BB_HRD  |     -10 |  
+-------------------------+------+----------+----------+---------+---------+  

Answer 3

If the names inside <> are always like those from your sample, i.e., <Name>something</Tred><useridNo>something</NewUserid> and so on, try this:

SELECT [Date1], 
 SUBSTRING([OperationDetail], CHARINDEX('<Name>', [OperationDetail]) + LEN('<Name>'), CHARINDEX('</Tred>', [OperationDetail]) - (CHARINDEX('<Name>', [OperationDetail]) + LEN('<Name>'))) AS [Name],
 SUBSTRING([OperationDetail], CHARINDEX('<useridNo>', [OperationDetail]) + LEN('<useridNo>'), CHARINDEX('</NewUserid>', [OperationDetail]) - (CHARINDEX('<useridNo>', [OperationDetail]) + LEN('<useridNo>'))) AS [useridNo],
 SUBSTRING([OperationDetail], CHARINDEX('<username>', [OperationDetail]) + LEN('<username>'), CHARINDEX('</newusername>', [OperationDetail]) - (CHARINDEX('<username>', [OperationDetail]) + LEN('<username>'))) AS [username],
 SUBSTRING([OperationDetail], CHARINDEX('<EnvCode>', [OperationDetail]) + LEN('<EnvCode>'), CHARINDEX('<ReportId>', [OperationDetail]) - (CHARINDEX('<EnvCode>', [OperationDetail]) + LEN('<EnvCode>'))) AS [EnvCode],
 SUBSTRING([OperationDetail], CHARINDEX('<LogCode>', [OperationDetail]) + LEN('<LogCode>'), CHARINDEX('</Logidfile>', [OperationDetail]) - (CHARINDEX('<LogCode>', [OperationDetail]) + LEN('<LogCode>'))) AS [LogCode]
FROM #Longdata;

Answer 4

Check one more approach:

drop table if exists #Longdata

create table #Longdata ([Date1] [datetime], OperationDetail [text] NULL)

insert into #Longdata values ('2019-10-11','<Name>AAA</Tred><useridNo>3929</NewUserid><username>Rob</newusername><EnvCode>BB_HRD<ReportId>10865</ReportIdN><LogCode>-10</Logidfile></Gter>')

insert into #Longdata values ('2019-10-12','<Code>1234</x><Id>777')

select * from #Longdata

---

declare @q as varchar(max)

;
with Q1 as
(
    select Date1, 
        cast(OperationDetail as varchar(max)) s, 
        cast(null as varchar(max)) as name, 
        cast(null as varchar(max)) as value
    from #Longdata
    union all
    select Date1, 
        substring(s, i3, len(s)),
        substring(s, i1 + 1, i2 - i1 - 1), 
        substring(s, i2 + 1, i3 - i2 - 1)
    from Q1
    cross apply (values (patindex('%<[^/]%', s))) t1(i1)
    cross apply (values (charindex('>', s, i1))) t2(i2)
    cross apply (values (charindex('<', s, i2))) t3(i3z)
    cross apply (values (iif(i3z = 0, len(s) + 1, i3z))) t4(i3)
    where i1 > 0 and i2 > 0
), Q2 as
(
    select 'select ' + quotename(convert(varchar(max), Date1, 23), '''') + ' as Date1, ' +  STRING_AGG(quotename(value, '''') + ' as ' + quotename(name), ', ') as q
    from Q1
    group by Date1
)
select @q = string_agg(q, '; ')
from Q2

exec (@q)

/*

Outputs:

Date1      Name useridNo username EnvCode ReportId LogCode
---------- ---- -------- -------- ------- -------- -------
2019-10-11 AAA  3929     Rob      BB_HRD  10865    -10


Date1      Code Id
---------- ---- ----
2019-10-12 1234 777

*/