Geek Answers Handbook

Dynamically identify the number of key-value pairs within a string

I have a requirement to take a delimited row divided into rows and convert the data to columns.

I have a working method that I can use, however, this assumes that the incoming data will be structured the same every time it is delivered, and it will need to be updated if a new key-value pair is added, and I would like to to be proven in the future as much as possible.

Here are the DDL and sample data and the solution I have been working on so far.

DDL:

    CREATE TABLE #pipers (team_id nvarchar(50), piped_string nvarchar(250))

Sample data:

    INSERT INTO #pipers (team_id, piped_string) VALUES 
    (newid(), 'team_name:Brighton|stadium:American Express Community Stadium|max_attendance:30750')
    ,(newid(), 'team_name:Middlesbrough|stadium:Riverside|max_attendance:34742')
    ,(newid(), 'team_name:Derby|stadium:Pride Park Stadium|max_attendance:33597')

Current attempt:

    ;WITH xmldata (team_id, piped_string, xml_string)
    AS
    (
    SELECT team_id
        ,piped_string
        ,CAST('<teaminfo>'+'<team>'+REPLACE(piped_string,'|','</team><team>')+'</team>'+'</teaminfo>' AS XML)
    FROM #pipers
    )

    SELECT team_id, 
       piped_string,
       team_name = x.xml_string.value('(/teaminfo/team)[1]','nvarchar(150)'),
       stadium = x.xml_string.value('(/teaminfo/team)[2]','nvarchar(150)'),
       max_attendance = x.xml_string.value('(/teaminfo/team)[3]','nvarchar(150)')
    FROM xmldata x

My question is: how could I dynamically identify the number of pairs inside the input string and apply this to the XML logic?

+4
1

, , :

DECLARE @str VARCHAR(MAX)='team_name:Brighton|stadium:American Express Community Stadium|max_attendance:30750';
SELECT LEN(@str)-LEN(REPLACE(@str,'|','')) + 1;
+4

More articles:


All Articles