Suppose there is an XML of this type:

& lt; root & gt;
& lt; tablero & gt;
& lt; field1 & gt; & lt; / field1 & gt;
& lt; field2 & gt; & lt; / field2 & gt;
& lt; field3 & gt; & lt; / field3 & gt;
& lt; / tablerow & gt;
& lt; tablero & gt;
& lt; field1 & gt; & lt; / field1 & gt;
& lt; field2 & gt; & lt; / field2 & gt;
& lt; field3 & gt; & lt; / field3 & gt;
& lt; / tablerow & gt;
& lt; / root & gt;

Is it possible to get a sample of this type?

value
---------------
Value field1
Value field2.
Value field3.
Value field1
Value field2.
Value field3.

Is it possible to do this without explicitly enroll the name of the nodes? Suppose it is known that all Field + digit nodes. Is it possible to make a request on a mask?

Answer 1, Authority 100%

You can filter nodes with the desired names using the Local-Name and Substring :

declare @xml xml = n '
& lt; root & gt;
 & lt; tablero & gt;
  & lt; field1 & gt; Value 1 & lt; / field1 & gt;
  & lt; field2 & gt; Value 2 & lt; / Field2 & gt;
  & lt; field3 & gt; Value 3 & lt; / Field3 & gt;
  & lt; not_a_field & gt; aa & lt; / not_a_field & gt;
 & lt; / tablerow & gt;
 & lt; tablero & gt;
  & lt; field1 & gt; Value 4 & lt; / Field1 & gt;
  & lt; field2 & gt; Value 5 & lt; / Field2 & gt;
  & lt; field3 & gt; Value 6 & lt; / Field3 & gt;
  & lt; not_a_field & gt; bb & lt; / not_a_field & gt;
 & lt; / tablerow & gt;
& lt; / root & gt; ';
SELECT X.C.VALUE ('Text () [1]', 'Varchar (20)') Value
From @ xml.nodes ('/ root [1] / TableRow / * [Substring (Local-Name (), 0, 6) = "field"]') x (c);

Result:

value
--------
Value 1.
Value 2.
Value 3.
Value 4.
Value 5.
Value 6.

In XQuery requests to use * in XPath (as well as expressions like '// Field' ), if without them you can do it, not recommended. If you choose nodes from the root, then it is better to specify '/ root [1]' at the beginning of XPath. If the path is exactly known to the nodes – specify it clearly. If you take a value from the element, it is better to take it not through . , and via text>[1] . The more information about the nodes you give an XQuery processor, the easier it will be to paint XML and the lower the cost of the query.

In this case, for example, the estimated cost of such a request

select x.c.value ('text () [1]', 'Varchar (20)') Value
From @ xml.nodes ('/ root [1] / TableRow / * [Substring (Local-Name (), 0, 6) = "field"]') x (c);

and this

select x.c.value ('.', 'Varchar (20)') Value
from @ xml.nodes ('* / * / * [substring (Local-Name (), 0, 6) = "field"] ") x (c);

correlated approximately as 2 to 136.

Answer 2, Authority 150%

must work such a request:

select X.Value ('.', 'Nvarchar (MAX)')
From [Tablename]
Cross Apply [xmlfield] .nodes (* / * / * ') t (x);

In the expression * / * / * Each asterisk meets any name at this level of nesting.