Formula description:
The formula is used to retrieve data from XML text using an XPath expression. It allows you to read values of elements contained in the XML structure. The formula returns an array of all found values.
The formula is used to retrieve data from XML text using an XPath expression. It allows you to read values of elements contained in the XML structure. The formula returns an array of all found values.
Syntax:
FILTERXML(XML, xPath, ignoreNamespaces)
FILTERXML(XML, xPath, ignoreNamespaces)
Parameters:
XML(type: text)
IgnoreNamespaces(type: logical value)(optional parameter)
XML(type: text)
Text containing the XML from which the value should be read.
xPath(type:text)
XPath expression indicating which elements or values should
be returned.
Specifies whether namespace markers should be ignored. TRUE
– ignores namespaces and allows simpler XPath paths to be
used. FALSE – requires full element names exactly as defined
in the XML. The default value is TRUE.
Return type:
Values in array
How the formula works:
The first parameter is the XML text.
The second parameter is the XPath expression that indicates which data should be retrieved.
The / symbol is used to move to the next element in the XML structure.
Example: /root/item means the item element located inside root.
The // symbol searches for an element anywhere in the XML, regardless of its location.
Example: //item finds all item elements.
The @ symbol is used to retrieve the value of an element attribute.
Example: //user/@id returns the value of the id attribute.
You can also filter results.
Example: //item[price="20"] finds only those item elements where price equals 20.
The third parameter specifies whether namespace markers should be ignored. A namespace in XML is an additional marker placed before an element name, for example: <a:Item /> or <ns:Item />.It is used when XML comes from different systems or contains elements with the same names. The ignoreNamespaces parameter lets you decide whether these markers should be taken into account.
The first parameter is the XML text.
The second parameter is the XPath expression that indicates which data should be retrieved.
The / symbol is used to move to the next element in the XML structure.
Example: /root/item means the item element located inside root.
The // symbol searches for an element anywhere in the XML, regardless of its location.
Example: //item finds all item elements.
The @ symbol is used to retrieve the value of an element attribute.
Example: //user/@id returns the value of the id attribute.
You can also filter results.
Example: //item[price="20"] finds only those item elements where price equals 20.
The third parameter specifies whether namespace markers should be ignored. A namespace in XML is an additional marker placed before an element name, for example: <a:Item /> or <ns:Item />.It is used when XML comes from different systems or contains elements with the same names. The ignoreNamespaces parameter lets you decide whether these markers should be taken into account.
Przykłady:
=join(', ',filterxml('<root><item>A</item><item>B</item></root>','//item',TRUE))
Formula returns: A, B
=first(filterxml('<root> <user id="55">Adam</user>
</root>','//user/@id',TRUE))
Formula returns: 55
=CONCAT(filterxml('<root xmlns:a="x"> <a:Item> <a:Value>100</a:Value>
</a:Item> </root>','//a:Item/a:Value',FALSE))
Formula returns: 100
=CONCAT(filterxml('<root> <item><price>10</price><name>A</name></item>
<item><price>20</price><name>B</name></item>
</root>','//item[price="20"]/name',TRUE))
Formula returns: B
=CONCAT(FILTERXML(A2,B2,C2))
0 Comments