使用WHERE类别从给定的XML内容中选择特定数据 [英] Select specific data from given XML content using WHERE clasue
本文介绍了使用WHERE类别从给定的XML内容中选择特定数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下XML ..，我正在尝试从该XML中获取特定数据：
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 >= 2" nodeset="/uploaded_form_dc8u7x/household_number" required="true()" type="int"/>
<bind nodeset="/uploaded_form_dc8u7x/Survey_Name" relevant=" /uploaded_form_dc8u7x/household_number > 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>
我正在尝试使用值 mumbai02代替所有城市来获得标签 Mumbai。所以我期望输出结果如下：
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
WHERE子句...
can we get above output from XML content using WHERE clause...
我们只能使用值'mumbai02'来获得标签'mumbai'吗？
can we only get label 'mumbai' using value 'mumbai02' ??
推荐答案
尝试一下：
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= ...
这篇关于使用WHERE类别从给定的XML内容中选择特定数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
