CODEWITHSUNDEEP
xmlsql-server-2008split
who-aditya-nawandar
who-aditya-nawandar

Reputation: 1242

Extract individual values from XML in SQL Server

DECLARE @S VARCHAR(MAX) = '1#5;2#;3#4'

DECLARE @x xml = '<r><c>' + 
         REPLACE(REPLACE(@S, '#','</c><c>'),';','</c></r><r><c>') + 
         '</c></r>'

 SELECT x.value('c[1]','int') AS ParentCategoryID,
x.value('c[2]','int') AS CategoryID

FROM @x.nodes('/r') x(x)

This gives me the values as I want, in a table format.

This is the XML generated:

<r>
 <c>1</c>
<c>5</c>
</r>
<r>
<c>2</c>
<c />
</r>
<r>
<c>3</c>
<c>4</c>
</r>

I need to extract the individual values (say, in temporary variables) so that I can use them for insertion in tables. Thanks!

Upvotes: 0

Views: 76

Answers (1)

AlexK
AlexK

Reputation: 9937

--SELECT * FROM [dbo].[Split3] ('1,12,-3 ;4, 5,6;', ',', ';')

CREATE FUNCTION [dbo].[Split3] (
  @String nvarchar(MAX),
  @SepColumn nvarchar(1),
  @SepRow nvarchar(1)
)

RETURNS @T TABLE (
    col1 nvarchar(50)
    ,col2 nvarchar(50)
    ,col3 nvarchar(50)
)

AS
BEGIN
    DECLARE @ParseXML xml

    SET @String = LTRIM(RTRIM(@String))

    --Remove last row separator from string
    IF RIGHT(@String, 1) = @SepRow
        SET @String = LEFT(@String, LEN(@String)-1);

    -- Check if a string is a list of numbers
    IF PATINDEX('%[^-0-9 '+@SepColumn+@SepRow+']%',@String) = 0
    BEGIN
        SELECT @ParseXML = CONVERT(XML,'<r><c>' + REPLACE(REPLACE(@String,@SepColumn,'</c><c>'),@SepRow,'</c></r><r><c>') + '</c></r>')

        INSERT @T
        SELECT
            NULLIF(LTRIM(RTRIM(T.c.value('./c[1]','varchar(20)'))),'')
            ,NULLIF(LTRIM(RTRIM(T.c.value('./c[2]','varchar(20)'))),'')
            ,NULLIF(LTRIM(RTRIM(T.c.value('./c[3]','varchar(20)'))),'')
        FROM @ParseXML.nodes('//r') T(c);
    END

RETURN

END
GO

Upvotes: 1

Related Questions