xpath extract field name and "column" name from jdo mapping-CodePudding

First time dealing with xpath and XML data. I have below xpath query that I got through some Stack Overflow answers. Below, I want to extract all the column names

    with t(x) as ( 
    values
    ('<?xml version="1.0" encoding="UTF-8"?>
<mapping>
   <package name="mypackage">
      <class name="mytable">
         <jdbc-class-map type="base" pk-column="id" table="public.mytable" />
         <jdbc-version-ind type="version-number" column="version" />
         <jdbc-class-ind type="myclass" column="jdoclass" />
         <field name="majorVersion">
            <jdbc-field-map type="value" column="majorversion" />
         </field>
         <field name="minorVersion">
            <jdbc-field-map type="value" column="minorversion" />
         </field>
         <field name="patchVersion">
            <jdbc-field-map type="value" column="patchversion" />
         </field>
         <field name="version">
            <jdbc-field-map type="value" column="version0" />
         </field>
         <field name="webAddress">
            <jdbc-field-map type="value" column="webaddress" />
         </field>
      </class>
   </package>
</mapping>'::xml)
    )
    
    select 
        unnest(xpath('./package/class/field/text()', x)) as "fieldname",
        unnest(xpath('./package/class/field/jdbc-field-map/text()', x)) as "columns"
    from t

The above query returns fieldname empty and coluns as null. I understand there is some problem with the XML path. I expect to see field name and column lists

fieldName      columns
--------------------------
majorversion   majorversion
minorversion   minorversion
...

CodePudding user response：

If you want to turn XML into a "table", this is typically done much easier using xmltable()

select info.*
from t
  cross join xmltable('/mapping/package/class/field' passing x
                      columns fieldname text path '@name', 
                              "column"  text path './jdbc-field-map/@column') as info

Online example

CodePudding user response：

I was able to achieve the result by

with myTempTable(myXmlColumn) as (
values ('<?xml version="1.0" encoding="UTF-8"?>
<mapping>
   <package name="mypackage">
      <class name="mytable">
         <jdbc-class-map type="base" pk-column="id" table="public.mytable" />
         <jdbc-version-ind type="version-number" column="version" />
         <jdbc-class-ind type="myclass" column="jdoclass" />
         <field name="majorVersion">
            <jdbc-field-map type="value" column="majorversion" />
         </field>
         <field name="minorVersion">
            <jdbc-field-map type="value" column="minorversion" />
         </field>
         <field name="patchVersion">
            <jdbc-field-map type="value" column="patchversion" />
         </field>
         <field name="version">
            <jdbc-field-map type="value" column="version0" />
         </field>
         <field name="webAddress">
            <jdbc-field-map type="value" column="webaddress" />
         </field>
      </class>
   </package>
</mapping>'::xml))

SELECT 
    unnest(xpath('//package/class/field/jdbc-field-map/@column', myTempTable.myXmlColumn))::text AS columns,
    unnest(xpath('//package/class/field//@name', myTempTable.myXmlColumn))::text AS fieldName
FROM myTempTable

result

fieldName      columns
--------------------------
"majorversion"  "majorVersion"
"minorversion"  "minorVersion"
"patchversion"  "patchVersion"
"version0"      "version"
"webaddress"    "webAddress"