Select X.N.value('string(@id)', 'int') As v1 From @x1.nodes('root') X(N)

Here is a link to a similar case with an explanation on why by the designer of the XML...

select O.X.value('@orderNumber', 'varchar(20)') as OrderNumber,
       I.X.value('@itemNumber', 'varchar(10)') as ItemNumber,
       I.X.value('@qty', 'varchar(10)') as Qty,
       I.X.value('@rate', 'float') as Rate
from @x.nodes('/root/salesOrder')...

Using the parent axis when extracting values from XML is not good for performance. 
Use an extra cross apply instead.

select P.X.value('(PackID/text())[1]','int') as PackID,
   I.X.value('(ItemNumber/text())[1]','int')...

Hi Jeff!

Sorry for the late reply. I guess holidays came along and this thread fell out of focus.

Your observation are correct about the behaviour of splitting on text(). It will...

To fix the problem with XML special characters in your splitter you can use "select @List for xml path('')" to create entities for your invalid characters.

The entities will be...

Actually none since limitations of the data type but number 2 works for more years then number 1 so well ......

FLWOR Statement and Iteration (XQuery)

If you let this run for a while you will eventually get 1 row back from the query.

declare @T table(Value datetime2);

declare @I int = 0

while 1 = 1

begin

set...

declare @X xml = '

<row id="1000000" xml:space="preserve">

<c1>Exported</c1>

<c2>Text Sample 1</c2>

<c2 m="2">Text Sample 2</c2>

<c2 m="3">Text Sample 3</c2>

<c2 m="4">Text Sample 4</c2>

<c3>Text Sample 1</c3>

<c3 m="2">Text Sample 2</c3>

<c3 m="3">Text Sample 3</c3>

<c4>Text Sample</c4>

<c5>Text Sample</c5>

</row>';

set @X.modify('insert <ctest>New...

Alan.B (8/17/2015)

Performance aside, including the text() node is just good programming. Without it you are relying on the parser's default behavior to get you your result. Without it, what...

Put the query you have, without the group by, in a derived table and do the group by in the main query.

Not sure about your table structures and relations but...

FYI: the plan will be identical regardless of what your XML variable contains....

//name and /STUFF_FROM_MSDB/SYSTABS/name/text() is not equivalent. The first...

I guess this is the preferred form.

SELECT

col.value('(Title/text())[1]', 'varchar(50)') AS 'Book'

FROM @x.nodes('/root/Books/Book') a(col)

Using text() in the values clause removes one call to a table valued function...

The nodes specs and name have a namespace. You can use * as wildcard character for a namespace or you can use WITH XMLNAMESPACES (Transact-SQL).

.value('(/includeSelectionItemsTemp/ArrayOfObjectSelector/ObjectSelector/*:specs/*:name/text())[1]', 'uniqueidentifier')