CODEWITHSUNDEEP
sql-serverfunctiont-sqlxquery-sql
Shani Bhati
Shani Bhati

Reputation: 161

SQL how to extract value from xml string

CREATE FUNCTION split_string_XML
(
    @in_string VARCHAR(MAX),
    @delimiter VARCHAR(1)
)
RETURNS @list TABLE(NAMES VARCHAR(50))
AS
BEGIN
DECLARE @sql_xml XML = Cast('<root><U>'+ Replace(@in_string, @delimiter, '</U><U>')+ '</U></root>' AS XML)
    
    INSERT INTO @list(NAMES)
    SELECT f.x.value('.', 'VARCHAR(50)') AS NAMES
    FROM @sql_xml.nodes('/root/U') f(x)
    WHERE f.x.value('.', 'VARCHAR(50)') <> ''
    RETURN
END
GO

I am unable to understand the syntax and functionality for the below line

 SELECT f.x.value('.', 'VARCHAR(50)')

what does this '.' indicates and what is its purpose.

Upvotes: 0

Views: 3533

Answers (1)

Dai
Dai

Reputation: 155428

The f is not immediately obvious when reading your posted code because the author did not use an explicit AS keyword.

So this:

INSERT INTO @list(NAMES)
SELECT f.x.value('.', 'VARCHAR(50)') AS NAMES
FROM @sql_xml.nodes('/root/U') f(x)
WHERE f.x.value('.', 'VARCHAR(50)') <> ''

Can be expanded to:

INSERT INTO
    @list( NAMES )
SELECT
    f.x.value( '.', 'VARCHAR(50)' ) AS NAMES
FROM
    @sql_xml.nodes('/root/U') AS f( x )  /*  `f` is declared here */
WHERE
    f.x.value('.', 'VARCHAR(50)') <> ''

The @sql_xml.nodes('/root/U') AS f( x ) line does this:

  1. @sql_xml is a T-SQL variable with the XML data-type, which is a "special" type (compared to int or date, for example) because it supports methods (in an OOP sense).
  2. The XML.nodes(XQuery) method is called which extracts all <U> elements from the XML document structure.
  3. The AS f( x ) part is a declaration, which declares a new derived table called f that contains a single column named x mapped to the <U> elements extracted by the nodes() method. Note that the data-type of x is still XML.
  4. The .value( path, type ) method extracts the specified path and exposes it with type type (in this case, varchar(50)).

That said, if this function is being used to split a list of input values into a table, don't do that. Instead use table-valued parameters directly so you don't need to faff around with splitting strings.

Upvotes: 3

Related Questions