The add-in can return XML as column values. The add-in 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;
| |
Column Name | Example Value |
HighestScore | 14 |