Parse XML in AE scripting

[Michael A. Lowry]

During the course of writing my script to Generate an input dialog box dynamically from a prompt set object, I discovered that some properties of prompt sets cannot be read using PREP_PROCESS_PROMPTSET. I worked around this by making an SEC_SQLI VARA object to read the details directly from the database. However, I then found that some of the properties are stored in embedded XML snippets in the database. For example, the caption that appears in the title bar of a prompt set is stored in an XML snippet in the OPSA_XUI column. E.g.,

<dialog height="459" icon="PRPT" id="PRPTS" left="0" top="0" width="422">
  <readpanel fill="b" id="PRPTBOX" nl="1" scroll="v" text="Please enter values">
    <properties>
      <entry name="text">Please enter values</entry>
      <entry name="modifiable">0</entry>
    </properties>
  </readpanel>
</dialog>

 

I wrote a little JOBI to extract the content of the first instance of a named XML element.

 

UC0.GET_XML_ELEMENT.JOBI

! :PRINT "--- BEGIN UC0.GET_XML_ELEMENT.JOBI ---"
:SET &XML_Content# = &XML_Content#
:SET &XML_Element_Name# = &XML_Element_Name#
:IF &XML_Content# = ""
:  PRINT "Required variable &&XML_Content# is null."
:  SET &Error# = "YES"
:ENDIF
:IF &XML_Element_Name# = ""
:  PRINT "Required variable &&XML_Element_Name# is null."
:  SET &Error# = "YES"
:ENDIF
:IF &Error# = "YES"
:  PRINT "Required variable(s) not set."
:  STOP MSG,51,"Please set all required variables."
:ENDIF
:SET &XML_Element_Name_length# = STR_LENGTH(&XML_Element_Name#)
:SET &Element_begin# = STR_FIND(&XML_Content#,'<&XML_Element_Name#')
:IF &Element_begin# > 0
:  SET &Element_begin# = STR_FIND(&PromptSet_XUI#,">",&Element_begin#)
:  SET &Element_begin# = &Element_begin# + 1
:  SET &Element_end# = STR_FIND(&PromptSet_XUI#,'</&XML_Element_Name#>',&Element_begin#)
:  SET &Element_length# = &Element_end# - &Element_begin#
:  SET &XML_Element_Value# = STR_CUT(&XML_Content#,&Element_begin#,&Element_length#)
:ELSE
:  SET &XML_Element_Value# = ""
:ENDIF
! :PRINT "--- END UC0.GET_XML_ELEMENT.JOBI ---"

 

This works well enough, but I wondered if it might be possible to use the AE’s built-in XML processing capabilities to improve on this solution. Here’s what I came up with.

 

UC0.GET_XPATH_RESULT.JOBI

! :PRINT "--- BEGIN UC0.GET_XPATH_RESULT.JOBI ---"

! Check required variables
:SET &XML_Content# = &XML_Content#
:SET &Xpath# = &Xpath#
:IF &XML_Content# = ""
:  PRINT "Required variable &&XML_Content# is null."
:  SET &Missing_variables# = "YES"
:ENDIF
:IF &Xpath# = ""
:  PRINT "Required variable &&Xpath# is null."
:  SET &Missing_variables# = "YES"
:ENDIF
:IF &Missing_variables# = "YES"
:  PRINT "Required variable(s) not set."
:  STOP MSG,51,"Please set all required variables."
:ENDIF

! Set up temporary XML VARA
:SET &XML_VARA_Name# = "TEMP_&$USER#.VARA_XML"
:SET &VARA_OHIDNR# = GET_OH_IDNR(&XML_VARA_Name#)
:IF &VARA_OHIDNR# = 0
:  SET &CREATE_OBJECT_RC#= CREATE_OBJECT(XML,&XML_VARA_Name#,,"Temporary XML VARA for &$USER#","I","","FREE")
:ENDIF

! Put XML in VARA
:SET &XML_VARA_Key# = "&$RUNID#_&$DATE_YYYYMMDD#_&$TIME_HHMMSS#"
:PUT_VAR &XML_VARA_Name#,&XML_VARA_Key#,&XML_Content#

! Run Xpath query
:SET &Xpath_result# = GET_VAR(&XML_VARA_Name#,&XML_VARA_Key#,&Xpath#)

! Remove temporary VARA
:DELETE_VAR &XML_VARA_Name#, &XML_VARA_Key#

! :PRINT "--- END UC0.GET_XPATH_RESULT.JOBI ---"

 

This JOBI inserts the XML into an XML VARA, and then uses GET_VAR’s Xpath capabilities to run the query against the stored XML. Note that the JOBI also takes advantage of the (undocumented) fact that it’s possible to use CREATE_OBJECT to create XML VARAs.

 

If you need to grab pieces of lots of XML files, UC0.GET_XML_ELEMENT.JOBI (or something like it) is probably your best bet, because it’s very fast. However, if you need the specificity and power of Xpath, UC0.GET_XPATH_RESULT.JOBI works great (albeit with a small performance penalty due to creating and deleting a temporary VARA).

 

Enjoy!

[Leonard Olteanu]

How about getting XML elements directly in a SEC_SQLI VARA, using something like this:

select
     o.OPSA_OH_Idnr
     , cast(o.OPSA_XUI as xml).value('(/dialog/readpanel/properties/entry[@name="text"])[1]','varchar(2000)') as readpanel_text
     , cast(o.OPSA_XUI as xml).value('(/dialog/readpanel/properties/entry[@name="modifiable"])[1]','int') as readpanel_modifiable
from
     dbo.OPSA o

[Michael A. Lowry]

In this case, that’s an excellent suggestion for streamlining the process. Thanks!

[Michael A. Lowry]

Here’s an Oracle SQL query to fetch the PRPT window title.

select
xmlcast(xmltype(OPSA_XUI).extract('/dialog/readpanel/properties/entry[@name="text"]') as varchar2(100)) as readpanel_text
from OH left join OPSA on OH_Idnr = OPSA_OH_Idnr
where OH_Name = ?

[Leonard Olteanu]

Thanks for the Oracle version! I forgot to mention that my example is for SQL Server, but it should be obvious due to the use of "dbo." in the SQL. In general, I prefer to parse XML in SQL, if it is stored in the database. It simplifies the solution a lot.

[Michael A. Lowry]

This afternoon I finally figured out how to use the AE functions for XML processing. It works with XML DOCU tabs, XML VARAs, or XML files. The documentation has been quite lacking in this area. I'm sure it'll be improved soon!