how to select data with a attribute from xml columns in postgres

Question

I have a following xml content:

<?xml version="1.0" encoding="utf-8"?>
<h:html xmlns="http://www.w3.org/2002/xforms" xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:h="http://www.w3.org/1999/xhtml" xmlns:jr="http://openrosa.org/javarosa" xmlns:orx="http://openrosa.org/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <h:head>
    <h:title>Demo12</h:title>
    <model>
      <instance>
        <uploaded_form_bpdwls id="Demo12">
          <formhub>
            <uuid/>
          </formhub>
          <Household_Number/>
          <Survey_Name/>
          <start/>
          <end/>
          <meta>
            <instanceID/>
          </meta>
        </uploaded_form_bpdwls>
      </instance>
    </model>
  </h:head>
  <h:body>
    <input ref="/uploaded_form_bpdwls/Household_Number">
      <label>Household Number</label>
    </input>
    <input ref="/uploaded_form_bpdwls/Survey_Name">
      <label>Survey Name</label>
    </input>
  </h:body>
</h:html>

In above XML content,

inside body, there are two input tags with different attributes (i.e. @ref="/uploaded_form_bpdwls/Household_Number).
I am trying to select data in table format through postgresSQL. I want "House Hold" and "Survey Name" as separate columns.
I don't have an idea how to select data using attribute of tags.

Is it possible to select data under separate columns

What should be the select query to achieve this?

Rémy Baron · Accepted Answer · 2016-06-21 10:57:51Z

1

is it what you want ? :

with table1 as (
  select $$<?xml version="1.0" encoding="utf-8"?>
 <h:html xmlns="http://www.w3.org/2002/xforms" xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:h="http://www.w3.org/1999/xhtml" xmlns:jr="http://openrosa.org/javarosa" xmlns:orx="http://openrosa.org/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<h:head>
<h:title>Demo12</h:title>
<model>
  <instance>
    <uploaded_form_bpdwls id="Demo12">
      <formhub>
        <uuid/>
      </formhub>
      <Household_Number/>
      <Survey_Name/>
      <start/>
      <end/>
      <meta>
        <instanceID/>
      </meta>
    </uploaded_form_bpdwls>
  </instance>
</model>
</h:head>
<h:body>
<input ref="/uploaded_form_bpdwls/Household_Number">
  <label>Household Number</label>
</input>
<input ref="/uploaded_form_bpdwls/Survey_Name">
  <label>Survey Name</label>
</input>
 </h:body>
 </h:html>$$::xml as xml_content

 )

 select myarray[1] val1,myarray[2] val2 from (
    select xpath('/h:html/h:body/i:input/i:label/text()',xml_content,ARRAY[ARRAY['h','http://www.w3.org/1999/xhtml'],ARRAY['i','http://www.w3.org/2002/xforms']]) myarray from table1  
 ) a

For multi-level try this :

        with table1 as (
        select $$<?xml version="1.0" encoding="utf-8"?>
        <h:html xmlns="http://www.w3.org/2002/xforms" xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:h="http://www.w3.org/1999/xhtml" xmlns:jr="http://openrosa.org/javarosa" xmlns:orx="http://openrosa.org/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <h:head>
            <h:title>Demo12</h:title>
            <model>
              <instance>
                <uploaded_form_bpdwls id="Demo12">
                  <formhub>
                    <uuid/>
                  </formhub>
                  <Household_Number/>
                  <Survey_Name/>
                  <start/>
                  <end/>
                  <meta>
                    <instanceID/>
                  </meta>
                </uploaded_form_bpdwls>
              </instance>
            </model>
          </h:head>
          <h:body>
            <div>
            <input ref="/uploaded_form_bpdwls/Household_Number">
              <label>Household Number</label>
            </input>
            <input ref="/uploaded_form_bpdwls/Survey_Name">
              <label>Survey Name</label>
            </input>
            </div>
            <div>
            <input ref="/uploaded_form_bpdwls/Household_Number">
              <label>Household Number2</label>
            </input>
            <input ref="/uploaded_form_bpdwls/Survey_Name">
              <label>Survey Name2</label>
            </input>
            </div>
          </h:body>
        </h:html>$$::xml as xml_content

        )

        select myarray[1] val1,myarray[2] val2 from (
           select xpath('/i:div/i:input/i:label/text()',xml_content,ARRAY[ARRAY['h','http://www.w3.org/1999/xhtml'],ARRAY['i','http://www.w3.org/2002/xforms']]) myarray from 
              (
              select unnest(xpath('/h:html/h:body/i:div',xml_content,ARRAY[ARRAY['h','http://www.w3.org/1999/xhtml'],ARRAY['i','http://www.w3.org/2002/xforms']])) xml_content from table1
              ) div
           ) a

edited Jun 21, 2016 at 10:57

answered Jun 21, 2016 at 9:05

Rémy Baron

1,4099 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

1990rk4 Over a year ago

I need some more help from you : can please explain why you have used prefixes in xpath... and how it can be used for multilevel data...

Rémy Baron Over a year ago

Because in your xml, namespaces are defined : xmlns="w3.org/2002/xforms" for tags without prefix and xmlns:h="w3.org/1999/xhtml" for tags with prefix "h" , if you don't define and use prefixes the xpath query return nothing (empty array)

1990rk4 Over a year ago

Remy, can you please take a look at this: stackoverflow.com/questions/37983227/…

Collectives™ on Stack Overflow

how to select data with a attribute from xml columns in postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related