Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Store data in format of XML string in SAP HANA system
In older SAP HANA versions, no XML functions were provided. With HANA 2.0, these two functions are provided- XMLEXTRACT and XMLEXTRACTVALUE for extracting XML string in SAP HANA.
Use of XMLEXTRACT
XMLEXTRACT(<XML_document>, <XPath_query> [,<NamespaceDeclarations>])
Syntax to use −
<XML_document>
Specifies an XML document of type CLOB, NCLOB, VARCHAR, or NVARCHAR.
<XPath_query>
Specifies an XPath expression of type VARCHAR or NVARCHAR.
<NamespaceDeclarations>
Specifies a namespace declaration of type VARCHAR or NVARCHAR.
Description
Returns the matching XML element. The return value is of type VARCHAR/NVARCHAR or CLOB/NCLOB depending on the type given for <XML_document>.
If an XML element is empty (for example, <name></name>), then an empty result is returned. If an XML element is not found, then the function returns an error.
SELECT XMLEXTRACT( '<doc xmlns:ns1="http://namespace1.sap.com" xmlns:ns2="http://namespace2.sap.com"> <ns1:item><ns1:id>1</ns1:id><ns1:name>Box</ns1:name></ns1:item> <ns1:item><ns1:id>2</ns1:id><ns1:name>Jar</ns1:name></ns1:item> <ns2:item><ns2:id>3</ns2:id><ns2:name>Table</ns2:name></ns2:item> </doc>', '/doc/ns1:item[2]/ns1:name', 'xmlns:ns1="http://namespace1.sap.com" xmlns:ns2="http://namespace2.sap.com"' ) FROM DUMMY;
Use of XMLEXTRACTVALUE
XMLEXTRACTVALUE(<XML_document>, <XPath_query> [,<NamespaceDeclarations>]) <XML_document>
Specifies an XML document of type CLOB, NCLOB, VARCHAR, or NVARCHAR.
<XPath_query>
Specifies an XPath expression of type VARCHAR or NVARCHAR.
<NamespaceDeclarations>
Specifies a namespace declaration of type VARCHAR or NVARCHAR.
Description
Returns the text contents of the matching XML element. The return value is of type VARCHAR/NVARCHAR or CLOB/NCLOB depending on the type given for<XML_document>.
If an XML element is empty (for example, <name></name>), then an empty result is returned. If an XML element is not found, then the function returns an error.
This function is only supported for single elements of child XML nodes. For example, you can use XMLEXTRACTVALUE to query the <name> and <age> elements in the XML document below, as these are single elements of child nodes. However, you cannot query the <parent> or <child> elements of the document, as both of these elements contain multiple child nodes.
<parent> <child> <name>Tom</name> <age>18</age> </child> </parent>
366 Views
