Select specific data from given XML content using WHERE clasue

Question

I have following XML.. and I am trying to get specific data from that XML:

    <?xml version="1.0" encoding="utf-8"?>
<html>
  <head>
    <title>report</title>
    <model>
      <instance>
        <uploaded_form_dc8u7x id="reportform">
          <formhub>
            <uuid/>
          </formhub>
          <household_number/>
          <Survey_Name/>
          <photo/>
          <city/>
          <date/>
          <survey/>
          <start/>
          <end/>
          <meta>
            <instanceID/>
          </meta>
        </uploaded_form_dc8u7x>
      </instance>
      <bind constraint=" /uploaded_form_dc8u7x/household_number  &gt;= 2" nodeset="/uploaded_form_dc8u7x/household_number" required="true()" type="int"/>
      <bind nodeset="/uploaded_form_dc8u7x/Survey_Name" relevant=" /uploaded_form_dc8u7x/household_number  &gt; 02" required="true()" type="string"/>
      <bind nodeset="/uploaded_form_dc8u7x/photo" required="true()" type="binary"/>
      <bind nodeset="/uploaded_form_dc8u7x/city" required="true()" type="select"/>
      <bind nodeset="/uploaded_form_dc8u7x/date" required="true()" type="dateTime"/>
      <bind nodeset="/uploaded_form_dc8u7x/survey" required="true()" type="select1"/>
      <bind preload="timestamp" preloadParams="start" nodeset="/uploaded_form_dc8u7x/start" type="dateTime"/>
      <bind preload="timestamp" preloadParams="end" nodeset="/uploaded_form_dc8u7x/end" type="dateTime"/>
      <bind calculate="concat('uuid:', uuid())" nodeset="/uploaded_form_dc8u7x/meta/instanceID" readonly="true()" type="string"/>
      <bind calculate="'05ef936fe45e41d1bc08474399d64191'" nodeset="/uploaded_form_dc8u7x/formhub/uuid" type="string"/>
    </model>
  </head>
  <body>
    <input ref="/uploaded_form_dc8u7x/household_number">
      <label>Household Number</label>
    </input>
    <input ref="/uploaded_form_dc8u7x/Survey_Name">
      <label>Survey Name</label>
    </input>
    <upload mediatype="image/*" ref="/uploaded_form_dc8u7x/photo">
      <label>photo</label>
    </upload>
    <select ref="/uploaded_form_dc8u7x/city">
      <label>city</label>
      <item>
        <label>pune</label>
        <value>pune01</value>
      </item>
      <item>
        <label>mumbai</label>
        <value>mumbai02</value>
      </item>
      <item>
        <label>ahmednagar</label>
        <value>ahmednagar03</value>
      </item>
      <item>
        <label>delhi</label>
        <value>delhi04</value>
      </item>
    </select>
    <input ref="/uploaded_form_dc8u7x/date">
      <label>date</label>
    </input>
    <select1 ref="/uploaded_form_dc8u7x/survey">
      <label>survey</label>
      <item>
        <label>raj</label>
        <value>raj01</value>
      </item>
      <item>
        <label>raju</label>
        <value>raju02</value>
      </item>
    </select1>
  </body>
</html>

I am trying to get label 'Mumbai' using value 'mumbai02' instead of all cities. so I am expecting the output as followos:

value      label
-----------------
mumbai02  mumbai

can we get above output from XML content using WHERE clause...

can we only get label 'mumbai' using value 'mumbai02' ??

PostgreSQL supports xpath syntax with function xpath. For your case it can be called like (xpath('/html/body/select/item[value=''mumbai02'']/label/text()', your_xml))[1]::text — Abelisto
– Abelisto, Commented Jul 5, 2016 at 8:32

Rémy Baron · Accepted Answer · 2016-07-05 10:42:06Z

1

try this :

   with table1 as (select $$
       -- INSERT YOUR XML HERE --
   $$::xml xml_content)

    select * from (
          select (xpath('value/text()',one_item))[1]::text _value,
                 (xpath('label/text()',one_item))[1]::text _label from (
                    select unnest(xpath($$//select[@ref='/uploaded_form_dc8u7x/city']/item$$
                           ,xml_content)) one_item from table1
                 ) a 
            ) b 
    where _value= ...

answered Jul 5, 2016 at 10:42

Rémy Baron

1,4099 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Select specific data from given XML content using WHERE clasue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related