CODEWITHSUNDEEP
sql-server
PS078
PS078

Reputation: 461

SQL Server: Text() in Xquery used to add delimiter in XML

I have following existing code in one of the stored procedures to all delimiter / between error messages encounters in the validations:

;with delimiting_errors
        (Id,
        Delimited_Error_List)
        as
        (
        select
        e2.Id,
        '/' 
        + (select '  ' + Fn
                from Customer e
                where e.Id = e2.Id
                for xml path(''), type
                ).value('substring(text()[1],2)', 'varchar(max)') as Delimited_Error
        from Customer e2
        group by e2.Id
        )

SELECT * FROM delimiting_errors

Request you to please help me in understanding the command 

value('substring(text()[1], 2)', 'varchar(max)')

  1. I tried to search about text(), but couldn't find exact documentation for the same.

  2. Similarly, how substring function is working only on 2 parameters in substring(text()[1], 2), which actually requires 3 parameter.

Please help me with the concept behind this command, also please help me with some resource to read about Text().

Upvotes: 1

Views: 3395

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

What is going on here:

.value('substring(text()[1],2)', 'varchar(max)')

  • value() function to extract a specific value from the XML, and convert it to a SQL Server data type, in your case to varchar(max)
  • substring is XQuery substring, not SQL substring, here it returns substring starting at position 2
  • text() function here retrieves the inner text from within the XML
  • [1] suffix acts as an indexer, and fetches the first result matched

For more info read XQuery Language Reference, it's like "another language" inside SQL.

Upvotes: 3

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

.value('substring(text()[1],2)', 'varchar(max)') as Delimited_Error

You use this XML-trick to concatenate values. In the beginning you add a double space select ' ' + Fn, this must be taken away for the beginning of the return string.

So, the .value returns the "substring" (XPath-Function!) of the inner text() starting at the index 2.

Find more information here: http://wiki.selfhtml.org/wiki/XML/XSL/XPath/Funktionen

Upvotes: 1

Related Questions