none
How to get field and value from a given string in XML format RRS feed

  • Question

  • Hello there,

    I am trying to get column as the header and column value under the header. Header is the column name. Below is an example which provides me list of all the text under the same column. However, I would like to get the data 3 separate columns as per this example. This should be generic and can be more columns in real data.

    DECLARE @xml  as xml = 
    '<p>[Column 1]</p>
    <p>Column 1 Value</p>
    <p>[Column 2]</p>
    <p>Column 2 Value</p>
    <p>[Column 3]</p>
    <p>Column 3 Value</p>'

    select Replace(Replace(convert(varchar(max),result),'<p>',''),'</p>','') AS result 
    FROM (
    SELECT T.c.query('.') AS result  
    FROM   @xml.nodes('/p') T(c) 
    )c

    This query returns all the values in single column. How to make this as Key and value. The expectation from this string is below. Please note, field name comes with braces [].

    [Column 1]                    [Column 2]                  [Column 3]        

    Column 1 Value           Column 2 Value               Column 3 Value

    Wednesday, July 31, 2019 4:45 PM

Answers

  • Since you do not need a dynamic sql script, it is easier now:

    DECLARE @xml XML = '
    <p>[Column 1]</p>
    <p>Column 1 Value</p>
    <p>[Column 2]</p>
    <p>Column 2 Value</p>
    <p>[Column 3]</p>
    <p>Column 3 Value</p>';
    
    DECLARE @Items TABLE (
    	RowId int IDENTITY(1, 1),
    	Item varchar(1000)
    );
    
    INSERT INTO @Items (Item)
    SELECT b.value('(./text())[1]','Varchar(50)') as [Item]
    FROM @xml.nodes('/p') as a(b) 
    
    SELECT f.Field, v.Value
    FROM (
    	SELECT RowId, Item AS Field FROM @Items WHERE RowId % 2 <> 0
    ) AS f
    INNER JOIN (
    	SELECT RowId, Item AS Value FROM @Items WHERE RowId % 2 = 0
    ) AS v ON v.RowId - 1 = f.RowId;


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Thursday, August 1, 2019 8:13 PM
    • Marked as answer by Palash Aich Friday, August 2, 2019 2:50 PM
    Thursday, August 1, 2019 8:12 PM
  • Hi Palash,

    Same story for the changed output format.

    No need for table variables, multiple derived tables, sophisticated joins, etc.

    Native XQuery shows its simplicity and beauty:

    DECLARE @xml XML = '<root><p>[Column 1]</p>
    	<p>Column 1 Value</p>
    	<p>[Column 2]</p>
    	<p>Column 2 Value</p>
    	<p>[Column 3]</p>
    	<p>Column 3 Value</p></root>';
    
    ;WITH rs (xmlData) AS
    (
    	SELECT @xml.query('<root>
    	{
    		for $r in /root/p[position() mod 2 eq 1]
    		let $pos := count(root/p[. << $r]) + 1
    		return <r>
    				<col>{data($r)}</col>
    				<val>{data($r/../p[$pos+1])}</val>
    			</r>
    	}
    	</root>')
    )
    SELECT col.value('(col)[1]', 'VARCHAR(30)') AS columnName
    	, col.value('(val)[1]', 'VARCHAR(100)') AS columnValue
    FROM rs CROSS APPLY xmldata.nodes('/root/r') tab(col);

    Output:

    columnName	columnValue
    [Column 1]	Column 1 Value
    [Column 2]	Column 2 Value
    [Column 3]	Column 3 Value
    Thursday, August 1, 2019 9:42 PM

All replies

  • Hi Palash,

    I am not sure if it is possible to handle variable number of <p> elements and pivot them on the fly.

    DECLARE @xml XML = '<p>[Column 1]</p>
    	<p>Column 1 Value</p>
    	<p>[Column 2]</p>
    	<p>Column 2 Value</p>
    	<p>[Column 3]</p>
    	<p>Column 3 Value</p>';
    
    SELECT col.value('(p)[2]', 'VARCHAR(30)') AS [column1]
    	,col.value('(p)[4]', 'VARCHAR(30)') AS [column2]
    	,col.value('(p)[6]', 'VARCHAR(30)') AS [column3]
    FROM @xml.nodes('/') tab(col);

    Output:
    column1	column2	column3
    Column 1 Value	Column 2 Value	Column 3 Value
    Wednesday, July 31, 2019 7:01 PM
  • Thanks, Yitzak.

    The number of columns will be more or less in the actual data. I need a dynamic approach. Column name has brackets, []. Can we do something using this []. The values inside the column will not have brackets.

    Also, I can build the string as [Column 1]Column 1 Value[Column 2]Column 2 Value[Column 3]Column 3 Value.

    Is there a way to extract field and value from this string? First column name will be available with the brackets, followed by values without the brackets.

    Thanks in Advance.

    Palash



    Thursday, August 1, 2019 5:50 AM
  • Hi Palash,

    IMHO, just some kind of dynamic SQL with string manipulations and using mod operation...


    Thursday, August 1, 2019 5:57 AM
  • Hi Palash,

    I came up with something. Please give it a shot.

    DECLARE @xml XML = '<p>[Column 1]</p>
    	<p>Column 1 Value</p>
    	<p>[Column 2]</p>
    	<p>Column 2 Value</p>
    	<p>[Column 3]</p>
    	<p>Column 3 Value</p>';
    
    -- count total number of items
    DECLARE @columnName VARCHAR(30)
    	, @SQL NVARCHAR(MAX)
    	, @i INT
    	, @cnt INT = @xml.value('count(/p)', 'INT');
    
    SET @SQL = 'SELECT ';
    
    -- loop XML item by item
    SET @i = 1;
    WHILE @i <= @cnt BEGIN
          SELECT @columnName = col.value('(./text())[1]','VARCHAR(30)')
          FROM @xml.nodes('/p[position() = sql:variable("@i")]') AS tab(col)
    
         -- do whatever needed in the loop here
    	 SET @SQL += CHAR(13) + CHAR(10) + 'col.value(''(p)[' + CAST((@i+1) AS VARCHAR(10)) +']'', ''VARCHAR(100)'') AS "' + @columnName + '",'
    
       SET @i += 2;
    END
    
    -- remove trailing comma
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 1);
    SET @SQL += CHAR(13) + CHAR(10) + 'FROM @xml.nodes(''/'') tab(col);'
    print @SQL;
    
    EXEC master.sys.sp_executesql @sql,  N'@xml XML', @xml;

    Output:

    [Column 1]	[Column 2]	[Column 3]
    Column 1 Value	Column 2 Value	Column 3 Value
    Thursday, August 1, 2019 3:54 PM
  • DECLARE @xml XML = '
    <p>[Column 1]</p>
    <p>Column 1 Value</p>
    <p>[Column 2]</p>
    <p>Column 2 Value</p>
    <p>[Column 3]</p>
    <p>Column 3 Value</p>';
    
    DECLARE @s varchar(max);
    DECLARE @rowId int;
    DECLARE @maxRowId int;
    DECLARE @item varchar(1000);
    DECLARE @sql varchar(max) = '';
    DECLARE @sqlColumns varchar(max) = '';
    DECLARE @sqlValues varchar(max) = '';
    DECLARE @Items TABLE (
    	RowId int IDENTITY(1, 1),
    	Item varchar(1000)
    );
    
    SELECT @s = CONVERT(varchar(max), @xml);
    SELECT @s = REPLACE(REPLACE(@s, '<p>', ''), '</p>', '|');
    
    INSERT INTO @Items (Item)
    SELECT value FROM STRING_SPLIT(@s, '|') WHERE LEN(value) > 0;
    
    SET @rowId = 1;
    SELECT @maxRowId = MAX(RowId) FROM @Items
    
    SET @sql = N'SELECT ';
    
    WHILE @rowId <= @maxRowId
    BEGIN
    	SELECT @item = Item FROM @Items WHERE RowId = @rowId;
    
    	IF @rowId % 2 <> 0
    	BEGIN
    		IF @rowId = @maxRowId - 1
    		BEGIN
    			SET @sql = @sql + '''***'' AS ' + QUOTENAME(@item);
    		END
    		ELSE
    		BEGIN
    			SET @sql = @sql + '''***'' AS ' + QUOTENAME(@item) + ', ';
    		END
    	END
    
    	IF @rowId % 2 = 0
    	BEGIN
    		SET @sql = REPLACE(@sql, '***', @item);
    	END
    
    	SET @rowId = @rowId + 1;
    END
    
    PRINT (@sql);
    EXEC (@sql);
    


    A Fan of SSIS, SSRS and SSAS

    Thursday, August 1, 2019 5:08 PM
  • Thanks, Guoxiong.

    This is really helpful.

    I am thinking for another approach to get all the field name in one column and value in another column. Instead of increasing columns, rows will increase.

    I used the code you provided and created a similar split function as I have a sql version issue.

    Below code splits the values and shows in one column.

    SELECT items FROM [dbo].[Fn_Split]
    (
     REPLACE(
    REPLACE(
    '[column1]Column 1 Value[column2]Column 2 Value[column3]Column 3 Value', ']', ']|')
    ,'[', '|[') -- Second Replace

    , '|') -- First Replace
    WHERE LEN(items) > 0;

    Result:

    [column1]
    Column 1 Value
    [column2]
    Column 2 Value
    [column3]
    Column 3 Value

    How can I get the result in below format.

    Field         Value
    [column1] Column 1 Value
    [column2] Column 2 Value
    [column3] Column 3 Value

    Thanks again.

    Palash

    Thursday, August 1, 2019 6:28 PM
  • Hi Palash,

    I hope you didn't miss my proposed solution.

    It is much simpler without any strings massage. No multiple CONVERT(), REPLACE(), STRING_SPLIT(), MAX(), etc.

    It works with SQL Server 2005 onwards.

    It just queries your native input XML as-is via simple XQuery.

    Thursday, August 1, 2019 6:32 PM
  • What is your SQL Server version? I forgot to tell you that the function STRING_SPLIT() only works on SQL 2016 or higher.

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 1, 2019 7:46 PM
  • Field         Value

    [column1] Column 1 Value
    [column2] Column 2 Value
    [column3] Column 3 Value

    DECLARE @xml XML = '
    <p>[Column 1]</p>
    <p>Column 1 Value</p>
    <p>[Column 2]</p>
    <p>Column 2 Value</p>
    <p>[Column 3]</p>
    <p>Column 3 Value</p>';
    
    DECLARE @s varchar(max);
    DECLARE @sqlValues varchar(max) = '';
    DECLARE @Items TABLE (
    	RowId int IDENTITY(1, 1),
    	Item varchar(1000)
    );
    
    SELECT @s = CONVERT(varchar(max), @xml);
    SELECT @s = REPLACE(REPLACE(@s, '<p>', ''), '</p>', '|');
    
    INSERT INTO @Items (Item)
    SELECT value FROM STRING_SPLIT(@s, '|') WHERE LEN(value) > 0;
    
    SELECT f.Field, v.Value
    FROM (
    	SELECT RowId, Item AS Field FROM @Items WHERE RowId % 2 <> 0
    ) AS f
    INNER JOIN (
    	SELECT RowId, Item AS Value FROM @Items WHERE RowId % 2 = 0
    ) AS v ON v.RowId - 1 = f.RowId;



    A Fan of SSIS, SSRS and SSAS

    Thursday, August 1, 2019 7:53 PM
  • Since you do not need a dynamic sql script, it is easier now:

    DECLARE @xml XML = '
    <p>[Column 1]</p>
    <p>Column 1 Value</p>
    <p>[Column 2]</p>
    <p>Column 2 Value</p>
    <p>[Column 3]</p>
    <p>Column 3 Value</p>';
    
    DECLARE @Items TABLE (
    	RowId int IDENTITY(1, 1),
    	Item varchar(1000)
    );
    
    INSERT INTO @Items (Item)
    SELECT b.value('(./text())[1]','Varchar(50)') as [Item]
    FROM @xml.nodes('/p') as a(b) 
    
    SELECT f.Field, v.Value
    FROM (
    	SELECT RowId, Item AS Field FROM @Items WHERE RowId % 2 <> 0
    ) AS f
    INNER JOIN (
    	SELECT RowId, Item AS Value FROM @Items WHERE RowId % 2 = 0
    ) AS v ON v.RowId - 1 = f.RowId;


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Thursday, August 1, 2019 8:13 PM
    • Marked as answer by Palash Aich Friday, August 2, 2019 2:50 PM
    Thursday, August 1, 2019 8:12 PM
  • Hi Palash,

    Same story for the changed output format.

    No need for table variables, multiple derived tables, sophisticated joins, etc.

    Native XQuery shows its simplicity and beauty:

    DECLARE @xml XML = '<root><p>[Column 1]</p>
    	<p>Column 1 Value</p>
    	<p>[Column 2]</p>
    	<p>Column 2 Value</p>
    	<p>[Column 3]</p>
    	<p>Column 3 Value</p></root>';
    
    ;WITH rs (xmlData) AS
    (
    	SELECT @xml.query('<root>
    	{
    		for $r in /root/p[position() mod 2 eq 1]
    		let $pos := count(root/p[. << $r]) + 1
    		return <r>
    				<col>{data($r)}</col>
    				<val>{data($r/../p[$pos+1])}</val>
    			</r>
    	}
    	</root>')
    )
    SELECT col.value('(col)[1]', 'VARCHAR(30)') AS columnName
    	, col.value('(val)[1]', 'VARCHAR(100)') AS columnValue
    FROM rs CROSS APPLY xmldata.nodes('/root/r') tab(col);

    Output:

    columnName	columnValue
    [Column 1]	Column 1 Value
    [Column 2]	Column 2 Value
    [Column 3]	Column 3 Value
    Thursday, August 1, 2019 9:42 PM
  • Thanks, Yitzak and Guoxiong. Both the solutions provided by you works fine.

    Thanks Again.

    Palash

    Friday, August 2, 2019 2:50 PM