Excel Add-In for REST

Build 24.0.9060

XML Functions

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 NameExample Value
GradeA
Score2

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 NameExample Value
NumberOfGrades5

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 NameExample 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 NameExample Value
LowestScore2

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 NameExample Value
HighestScore14

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060