---

The XQuery value() method

The XQuery value() method allows to extract values from xml records.

We have a table AnagraficheXml which contains 2 columns: first column is the primary key, the second one (PersoneXml) is the container of our xml.
Every record contains personal data of some persons.

Below some samples of the xml data.

First record:

<anagrafica>
  <person>
    <firstname>Alessandro</firstname>
    <surname>Chiappin</surname>
    <age>30</age>
  </person>
</anagrafica>

Second record:

<anagrafia>
  <person>
    <firstname>Francesco</firstname>
    <surname>Sartor</surname>
    <age>33</age>
  </person>
</anagrafica>

Third record:

<anagrafica>
  <person>
    <firstname>Simone</firstname>
    <surname>Casara</surname>
    <age>35</age>
  </person>
</anagrafica>

If we want to query the xml fields we can use the XQuery value() method which, through an XPath query, can extract the data that we are interested in, in tabular form
For example the following query extract the first occurence of the field 'firstname' in every record.

SELECT PersoneXml.value('(/anagrafica/person/firstname)[1]', 'varchar(max)') AS firstname FROM AnagraficheXml

This is the final resultset:

FirstName

Simone

Alessandro

Francesco

It is possible to query xml fields without an exact path but, for example, having only a node name. In this case the XPath return the very first occurence of our request.
The XPath key to obtain the node is '//'.
In our sample the resultset is however correct.

SELECT PersoneXml.value('(//firstname)[1]','varchar(max)') AS firstname FROM AnagraficheXml