Query XML messages by Property Path

Question

Question

Joao Palma · Aug 13, 2018

#SQL #XML #Caché

Hi

I need to query my messages and filter by a XML node.

In the message viewer it would be Critetion Type = 'VDoc Property Path' and class = 'Enslib.Edi.XML.Document'.

At the moment my query looks like this

SELECT *

FROM EnsLib_EDI_XML.Document

where doctype = 'avc:mydoctype'

I need then to look for 'HeadNode:SubNodeValue'

Anyone who could help me out with this?

Thank you

Discussion (5)1

Log in or sign up to continue

Tani Frankel · Aug 14, 2018

If I understand your question correctly then I think you'd benefit from defining and using a Search Table.

See from the Documentation -

If you need to query specifically via SQL (and not using the Message Viewer) then see also this answer (though relating to HL7, but relevant in the same way to the XML case) regarding using the Search Table within a query.

0 0

score 0 · Answer 1 · 2018-08-14T07:29:31-04:00

Thank you Tani.

The issue here is Ensemble does not retroactively index messages that were received before you added the search table class.

I need some information from the Past. I must create a report on the XML received messages since April based on one of the xml nodes values which distinguishes the document types.

score 1 · Answer 2 · 2018-08-14T07:52:17-04:00

The issue of indexing data previously processed (before the Search Table was defined for the Business Service/Operation) is addressable by calling the BuildIndex() method of the relevant SearchTable class.

For example:

Set sc=##class(YourPackage.YourSearchTableClass).BuildIndex()

score 0 · Answer 3 · 2018-08-14T08:13:19-04:00

Joao Palma · Aug 14, 2018

That's brilliant. Thank you very much.

0 0

score 1 · Answer 4 · 2018-08-14T08:23:17-04:00

Happy this helped.

In general (you might be aware of this, but for the benefit of the Community) a good resource about searching Messages (including Search Tables and SQL, and other options), is this Online Course.