cancel
Showing results for 
Search instead for 
Did you mean: 

XML Search in HANA

0 Kudos

Hi,

I have a use case where I have data stored as XML data type (BLOB) in a HANA table and we want to search specific nodes for specific values. One sample XML value would be

<?xml version="1.0" encoding="utf-8" ?>
<DocumentAttributes>
  <LetterID>473</LetterID>
  <DescriptionTexts>
    <DescriptionText1>
      <VersionNumber>493</VersionNumber>
      <VersionDate>20120220</VersionDate>
      <VersionTime>185412</VersionTime>
      <User>ABC</User>
      <LetterID>483</LetterID>
    </DescriptionText1>
  </DescriptionTexts>
</DocumentAttributes>

For example in SQL Server I'm able to use the following query to find out the desired result:

select * from dbo.XML_DATEN where XML_Data.value('(/DocumentAttributes/LetterID)[1]', 'int') = 473

In this example the query returns me all rows where under the node DocumentAttributes the LetterID is set to 473.

Is something similar also possible with HANA either over CE Functions or SQL Script? I searched the SQL reference without any success.

Thanks and Regards,
Daniel

Accepted Solutions (1)

Accepted Solutions (1)

former_member93896
Active Contributor
0 Kudos

Hello Daniel,

although SAP HANA can certainly store XML data (in a string or BLOB fields), the current release 1.0 SPS 3 does not support specific XML syntax or xPath expressions in SQL selects. I'm sure that we will add this functionality in a future release.

Regards,
Marc
SAP Customer Solution Adoption (CSA)

Former Member
0 Kudos

I thought this post is quite old now so my question is, do we have any xml parsing available till date within HANA SQL script.

Regards

Amir

Former Member
0 Kudos

Hi Marc,

We have a somewhat similar scenario like Daniel's, wherein we may need to run SQL queries from BODS, HANA Studio on XML blobs.

Is this feature available yet?

If yes, can you please let me know the version in which it was implemented.

Regards,

Srini

Answers (3)

Answers (3)

Former Member
0 Kudos

This message was moderated.

Former Member
0 Kudos

Hi,

we need this ability, too.

Looking forward to a workaround till it gets implemented. Or is it implemented now?

Best regards,

Manuel

0 Kudos

Thanks a lot for the answer, Marc. We will see whether we can use workarrounds until the funcitionality is impletemended - however this is tricky because the relevant string might be under different nodes and not all of them are relevant. We will see...

Thank you!

Daniel