Help to create column and data from Long text

Mike 341 Reputation points
2020-10-21T00:45:04.873+00:00

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:33882-capture6.jpg
Date1 Name useridNo username EnvCode LogCode
2019-10-11 AAA 3929 Rob BB_HRD -10

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-21T06:13:19.187+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,561 Reputation points
    2020-10-21T03:19:06.257+00:00

    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 |  
    +-------------------------+------+----------+----------+---------+---------+  
    
    1 person found this answer helpful.
    0 comments No comments

  2. Guoxiong 8,126 Reputation points
    2020-10-21T03:25:51.213+00:00

    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;
    
    1 person found this answer helpful.
    0 comments No comments

  3. Viorel 109.8K Reputation points
    2020-10-21T19:17:02.327+00:00

    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
    
    */
    
    1 person found this answer helpful.
    0 comments No comments