The FILTERXML function returns matching values from XML text by applying an XPath expression.
Syntax
Argument
Description
xml
(Required) A string that contains valid XML text.
xpath
(Required) An XPath expression used to select elements or attributes from the XML text.
Usage notes
FILTERXML can return a single value or spill multiple matching results vertically.
XPath can target element text such as //book/title or attribute values such as //book/@category.
FILTERXML is useful when worksheet formulas need to consume XML payloads without custom parsing code.
Example
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
spread.options.allowDynamicArray = true;
initSpread(spread);
};
function initSpread(spread) {
spread.setSheetCount(1);
spread.suspendPaint();
spread.suspendCalcService();
initSheet1(spread.getSheet(0));
spread.resumeCalcService();
spread.resumePaint();
}
function initSheet1(sheet) {
var xmlData = [
'<catalog>',
'<book category="Fiction"><title>The Lost Realm</title><author>Ava Stone</author><price>18.50</price></book>',
'<book category="Science"><title>Data Patterns</title><author>Leo Kim</author><price>24.00</price></book>',
'<book category="History"><title>City of Kings</title><author>Mia Chen</author><price>21.75</price></book>',
'</catalog>'
].join('');
sheet.suspendPaint();
sheet.name('FILTERXML');
sheet.setColumnWidth(0, 108);
sheet.setColumnWidth(1, 520);
sheet.setColumnWidth(2, 160);
sheet.setColumnWidth(3, 100);
sheet.setColumnWidth(4, 120);
sheet.setRowHeight(1, 90);
sheet.setValue(0, 1, 'Extract values from XML with XPath');
sheet.setValue(1, 0, 'XML Source');
sheet.setValue(1, 1, xmlData);
sheet.setValue(3, 1, 'Titles');
sheet.setValue(3, 2, 'Authors');
sheet.setValue(3, 3, 'Prices');
sheet.setValue(3, 4, 'Categories');
sheet.setValue(9, 0, 'XPath Query');
sheet.setValue(9, 1, '//book[price>20]/title');
sheet.setValue(10, 0, 'Try');
sheet.setValue(10, 1, '//book[price>20 and @category!="Science"]/title');
sheet.setValue(11, 1, 'Query Result');
var titleStyle = new GC.Spread.Sheets.Style();
titleStyle.font = 'bold 14pt Calibri';
sheet.setStyle(0, 1, titleStyle);
var labelStyle = new GC.Spread.Sheets.Style();
labelStyle.font = 'bold 11pt Calibri';
sheet.setStyle(1, 0, labelStyle);
sheet.setStyle(9, 0, labelStyle);
sheet.setStyle(10, 0, labelStyle);
var xmlInputStyle = new GC.Spread.Sheets.Style();
xmlInputStyle.backColor = '#FEF3CD';
sheet.setStyle(1, 1, xmlInputStyle);
sheet.getCell(1, 1).wordWrap(true);
var queryInputStyle = new GC.Spread.Sheets.Style();
queryInputStyle.backColor = '#FEF3CD';
sheet.setStyle(9, 1, queryInputStyle);
var headerStyle = new GC.Spread.Sheets.Style();
headerStyle.backColor = 'rgb(222,235,246)';
headerStyle.font = 'bold 11pt Calibri';
headerStyle.hAlign = 1;
var borderBottom = new GC.Spread.Sheets.LineBorder();
borderBottom.color = 'black';
borderBottom.style = GC.Spread.Sheets.LineStyle.thin;
headerStyle.borderBottom = borderBottom;
sheet.setStyle(3, 1, headerStyle);
sheet.setStyle(3, 2, headerStyle);
sheet.setStyle(3, 3, headerStyle);
sheet.setStyle(3, 4, headerStyle);
sheet.setStyle(11, 1, headerStyle);
sheet.setFormula(4, 1, '=FILTERXML($B$2,"//book/title")');
sheet.setFormula(4, 2, '=FILTERXML($B$2,"//book/author")');
sheet.setFormula(4, 3, '=FILTERXML($B$2,"//book/price")');
sheet.setFormula(4, 4, '=FILTERXML($B$2,"//book/@category")');
sheet.setFormula(12, 1, '=FILTERXML($B$2,$B$10)');
sheet.resumePaint();
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
