ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to read NAMESPACE of xml

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to read NAMESPACE of xml

    I need to read the namespace xml saved as an IFS file.
    I try
    Code:
    select xmldta.*
    from xmltable ('/' passing xmlparse(document get_xml_file(/tmp/my.xml'))
    columns ns varchar(512) path 'xmlroot/@xmlns') xmldta
    for file
    <xmlroot xmlns="http://definition/">
    ....
    </xmlroot>

    but SQL returns NULL

    Do you have idea how should I do it?

  • #2
    Currently, there is no direct way for SQL to read IFS files.

    However, most articles suggest creating a UDTF to an RPG program (or other HLL program) that receives the IFS filename, reads the IFS file, and passes back the contents to your SQL statement.

    Comment


    • #3
      You could of course use XML-INTO - or just XML-SAX if all you really need is the namespace value.

      Comment


      • #4
        XML-INTO works thank you

        Comment


        • #5
          Is there a way for xmltable to extract the namespace URI? All the general XPath methods I found by googling like namespace-uri-for-prefix() and the namespace:: axis are not supported on IBM i

          Comment


          • #6
            For SQL there is no function for determing the Namespace URL, but you can determine it by using a composition of SQL Scalar functions, something like this:
            Code:
            With x as (Select XMLSerialize(Document as CLOB(4096)) XMLString
                         from YourTable),
                 y as (Select Locate('"', xmlString, Locate('xmlns:', XMLString)) +1 StrPos, x.*
                         from x)        
            Select Substr(xmlstring, strPos, Locate('"', XMLString, StrPos) - StrPos) NSURL1,
              from y;

            Comment


            • #7

              But in RPG using XML-INTO

              Code:
              dcl-ds xmlroot Qualified;
                 xmlns varchar(512);
              end-ds;
              
              xml-into xmlroot %xml ('/tmp/tmp.xml' :'doc=file case=any allowmissing=yes allowextra=yes);

              Comment


              • JonBoy
                JonBoy commented
                Editing a comment
                You need to add the namespace option for this to work.
            Working...
            X