XML Functions

The cmdlet can return XML as column values. The cmdlet enables you to use SQL functions to work with these column values. The following sections provide examples; for a reference, see STRING Functions. The examples in this section use the following array (see Parsing Hierarchical Data for more information on parsing XML objects and arrays):

 
<grades>
  <student>
    <grade>A</grade>
    <score>2</score>
  </student>
  <student>
    <grade>A</grade>
    <score>6</score>
  </student>
  <student>
    <grade>A</grade>
    <score>10</score>
  </student>
  <student>
    <grade>A</grade>
    <score>9</score>
  </student>
  <student>
    <grade>B</grade>
    <score>14</score>
  </student>
</grades>

XML_EXTRACT

The XML_EXTRACT function can extract individual values from an XML object. The following query returns the values shown below based on the XML path passed as the second argument to the function:

SELECT Name, XML_EXTRACT(grades,'[0].grade') AS Grade, XML_EXTRACT(grades,'[0].score') AS Score FROM Students;


Column Name	Example Value
Grade	A
Score	2

XML_COUNT

The XML_COUNT function returns the number of elements in an XML array within an XML object. The following query returns the number of elements specified by the XML path passed as the second argument to the function:

SELECT Name, XML_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;


Column Name	Example Value
NumberOfGrades	5

XML_SUM

The XML_SUM function returns the sum of the numeric values of an XML array within an XML object. The following query returns the total of the values specified by the XML path passed as the second argument to the function:

SELECT Name, XML_SUM(score,'[x].score') AS TotalScore FROM Students;


Column Name	Example Value
TotalScore	41

XML_MIN

The XML_MIN function returns the lowest numeric value of an XML array within an XML object. The following query returns the minimum value specified by the XML path passed as the second argument to the function:

SELECT Name, XML_MIN(score,'[x].score') AS LowestScore FROM Students;


Column Name	Example Value
LowestScore	2

XML_MAX

The XML_MAX function returns the highest numeric value of an XML array within an XML object. The following query returns the maximum value specified by the XML path passed as the second argument to the function:

SELECT Name, XML_MAX(score,'[x].score') AS HighestScore FROM Students;