Yes indeed and often overlooked.

The difference is quite large, without the text() function, each value method results in three XML function calls, sort, joins etc., 15 operators in total. With the text() function it does a single XML function call per branch and the total of 5 operators.

😎

Full test set

USE TEEST;

GO

SET NOCOUNT ON;

--http://www.sqlservercentral.com/Forums/FindPost1802350.aspx

IF OBJECT_ID(N'dbo.TBL_XML_NVARCHAR') IS NOT NULL DROP TABLE dbo.TBL_XML_NVARCHAR;

CREATE TABLE dbo.TBL_XML_NVARCHAR

(

XN_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_NVARCHAR_XN_ID PRIMARY KEY CLUSTERED

,XN_VALUE NVARCHAR(MAX) NOT NULL

);

DECLARE @NVCXML NVARCHAR(MAX) = N'

<BounceProperties>

<Name>Bounce Category</Name>

<Value>Hard bounce - User Unknown</Value>

<Name>Bounce Type</Name>

<Value>immediate</Value>

<Name>SMTP Code</Name>

<Value>550</Value>

<Name>SMTP Reason</Name>

<Value>smtp;550 email@testsite.net...User unknown</Value>

</BounceProperties>';

;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))AS X(N))

, NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3)

INSERT INTO dbo.TBL_XML_NVARCHAR(XN_VALUE)

SELECT

@NVCXML

FROM NUMS NM;

-- text() function

;WITH XML_CONVERT AS

(

SELECT

XN.XN_ID

,CONVERT(XML,XN.XN_VALUE,1) AS XML_VAL

FROM dbo.TBL_XML_NVARCHAR XN

)

SELECT TOP 1000

XC.XN_ID

,XC.XML_VAL.value('(/BounceProperties/Name/text())[1]','varchar(20)') AS Name1

,XC.XML_VAL.value('(/BounceProperties/Value/text())[1]','varchar(50)') AS Value1

,XC.XML_VAL.value('(/BounceProperties/Name/text())[2]','varchar(20)') AS Name2

,XC.XML_VAL.value('(/BounceProperties/Value/text())[2]','varchar(50)') AS Value2

,XC.XML_VAL.value('(/BounceProperties/Name/text())[3]','varchar(20)') AS Name3

,XC.XML_VAL.value('(/BounceProperties/Value/text())[3]','varchar(50)') AS Value3

,XC.XML_VAL.value('(/BounceProperties/Name/text())[4]','varchar(20)') AS Name4

,XC.XML_VAL.value('(/BounceProperties/Value/text())[4]','varchar(50)') AS Value4

FROM XML_CONVERT XC;

--direct reference without the text() function

SELECT TOP 1000

XN_ID

--,CAST(XN_VALUE AS XML)

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Name[1])[1]','varchar(20)') AS Name1

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Value[1])[1]','varchar(50)') AS Value1

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Name[2])[1]','varchar(20)') AS Name2

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Value[2])[1]','varchar(50)') AS Value2

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Name[3])[1]','varchar(20)') AS Name3

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Value[3])[1]','varchar(50)') AS Value3

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Name[4])[1]','varchar(20)') AS Name4

,CAST(XN_VALUE AS XML).value('(/BounceProperties/Value[4])[1]','varchar(50)') AS Value4

FROM dbo.TBL_XML_NVARCHAR