本文介绍了在postgres中以表格格式从xml内容获取数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下xml内容：
< html> < head> < title> Sanjay Gandhi Nagar RHS Navi Mumbai< / title> < model> < instance> < uploaded_form_usq3zf id = Sanjay_Gandhi_Nagar_RHS_Navi_M> < formhub> < uuid /> < / formhub> < group_jg6ts57> < city_name> 7149< / city_name> < select_administrative_ward_nam> 09< / select_administrative_ward_nam> < select_slum> 272171490901< / select_slum> < date_of_rapid_household_survey /> < Name_of_surveyor_s_who_collec_001 /> < house_number /> < / group_jg6ts57> < type_of_house_occupancy /> < group_xb9nq26> < group_do8xg48> < what_is_the_full_name_of_the_f /> < mobile_number /> < aadhar_card_number /> < / group_do8xg48> < group_io6zh11> < type_of_structure_of_the_house /> < ownership_status /> < no_of_family_fembers /> < do_you_have_girl_child_child_under_a /> < if_yes_how_many /> < house_area_in_sq_ft /> < current_place_of_defecation_t /> < where_the_individual_toilet_is /> < type_of_water_connection /> < facility_of_waste_collection /> < interested_in_individual_toile /> < if_yes_why /> < if_no_why /> < type_of_toilet_preference /> < have_you_applied_or_individual /> < how_many_installments_have_yo /> < / group_io6zh11> < / group_xb9nq26> < start /> < end /> < meta> < instanceID /> < / meta> < / uploaded_form_usq3zf> < / instance> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / city_name required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / select_administrative_ward_nam required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / select_slum required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / date_of_rapid_household_survey required = true（） type = date /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / Name_of_surveyor_s_who_collec_001 required = true（） type = select1 /> < bind约束=。& lt; = 9999 nodeset = / uploaded_form_usq3zf / group_jg6ts57 / house_number required = true（） type = int /> < bind nodeset = / uploaded_form_usq3zf / type_of_house_occupancy required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_do8xg48 / what_is_the_full_name_of_the_f related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'或/ uploaded_form_type_type =（）/（3）字符串 /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_do8xg48 / mobile_number related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（） type = string /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_do8xg48 / aadhar_card_number related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = false（） type = string /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / type_of_structure_of_the_house related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / ownership_status related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / no_of_family_fembers related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（） type = int /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / do_you_have_girl_child_under_a related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（）> = = < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / if_yes_how_many related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'和/ uploaded_form_usq3zf / group_b ='_' type = int /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / house_area_in_sq_ft related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（） type = int /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / current_place_of_defecation_t related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（） type = select /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / where_the_individual_toilet_is related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'并且选择了（/ uploaded_form_group_b_n_ation_b'____n_ation_b_f_b_b_b_b_n_b_b_b_b_b_b_b_n_b_b_b为'_b'_n true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / type_of_water_connection related = / uploaded_form_usq3zf / type_of_house_occupancy =‘01’ required = true（） type = select /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / facility_of_waste_collection related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（） type = select /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / interested_in_individual_toile related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'而不是（selected（/ uploaded_form_b_n_zh_cn_zh_cn / zh_cn / zh_cn / zh_cn / zh_cn / zh_cn / zh_cn / zh_CN） required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / if_yes_why related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'和/ uploaded_form_usq3zf / group_xin_in'/'_' type = select /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / if_no_why related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'和/ uploaded_form_usq3zf / group_x_b9nin ='11'/ group_xb9nq'in' type = select /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / type_of_toilet_preference related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'和/ uploaded_form_us_3qfz / group_in_qq9zf / group_in = 26' type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / have_you_applied_or_individual related = / uploaded_form_usq3zf / type_of_house_occupancy ='01' required = true（）> = 1 < bind节点集= / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / how_many_installments_have_yo related = / uploaded_form_usq3zf / type_of_house_occupancy ='01'和/ uploaded_form_us_3_id_app_us_b3_f1_id_app_id_app_y_b2_ve_b_b_n type = select1 /> < bind preload = timestamp preloadParams = start nodeset = / uploaded_form_usq3zf / start type = dateTime /> < bind preload = timestamp preloadParams = end nodeset = / uploaded_form_usq3zf / end type = dateTime /> < bind Calculation = concat（'uuid：'，uuid（）） nodeset = / uploaded_form_usq3zf / meta / instanceID readonly = true（） type = string /> < bind Calculation =’1c0b0cef39054d85bdf2b17bb17e4043’ nodeset = / uploaded_form_usq3zf / formhub / uuid type = string /> < / model> < / head> < body> < group外观= field-list ref = / uploaded_form_usq3zf / group_jg6ts57> < label>管理信息< / label> < select1 ref = / uploaded_form_usq3zf / group_jg6ts57 / city_name> < label>城市名称< / label> < item> < label>新孟买< / label> < value> 7149< / value> < / item> < / select1> < select1 ref = / uploaded_form_usq3zf / group_jg6ts57 / select_administrative_ward_nam> < label>选择管理区名称< / label> < item> < label> Digha< / label> < value> 09< / value> < / item> < / select1> < select1 ref = / uploaded_form_usq3zf / group_jg6ts57 / select_slum> < label> Select Slum< / label> < item> < label> Sanjay Gandhi Nagar< / label> < value> 272171490901< / value> < / item> < / select1> < input ref = / uploaded_form_usq3zf / group_jg6ts57 / date_of_rapid_household_survey> <家庭住户快速调查的日期< / label> < / input> < select1 ref = / uploaded_form_usq3zf / group_jg6ts57 / Name_of_surveyor_s_who_collec_001> < label>收集了快速家庭调查数据的调查员的姓名。 < item> < label> Amol< / label> < value> amol< / value> < / item> < item> < label> Dhananjay< / label> < value> dhananjay< / value> < / item> < item> < label> Reshma< / label> < value> reshma< / value> < / item> < item> < label> Shankar< / label> < value> shankar< / value> < / item> < item> < label> Sheetal< / label> < value>表格< / value> < / item> < item> < label> Vaibhav< / label> < value> vaibhav< / value> < / item> < / select1> < input ref = / uploaded_form_usq3zf / group_jg6ts57 / house_number> < label>门牌号码< / label> < / input> < / group> < select1 ref = / uploaded_form_usq3zf / type_of_house_occupancy> < label>结构占用类型< / label> < item> < label>已占用< / label> < value> 01< / value> < / item> < item> < label>锁屋< / label> < value> 02< / value> < / item> < item> < label>双人间< / label> < value> 03< / value> < / item> < item> < label>破房子< / label> < value> 04< / value> < / item> < item> < label>在建房屋< / label> < value> 05< / value> < / item> < item> < label>永久锁定< / label> < value> 06< / value> < / item> < item> < label>不提供信息< / label> < value> 07< / value> < / item> < / select1> < group ref = / uploaded_form_usq3zf / group_xb9nq26> < label>家庭信息< / label> < group外观=字段列表 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_do8xg48> < label>个人信息< / label> < input ref = / uploaded_form_usq3zf / group_xb9nq26 / group_do8xg48 / what_is_the_full_name_of_the_f> < label>户主的全名是什么？< / label> < / input> < input ref = / uploaded_form_usq3zf / group_xb9nq26 / group_do8xg48 / mobile_number> < label>手机号码< / label> < / input> < input ref = / uploaded_form_usq3zf / group_xb9nq26 / group_do8xg48 / aadhar_card_number> < label> Aadhar卡号< / label> < / input> < / group> < group ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11> < label>一般信息< / label> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / type_of_structure_of_the_house> < label>房屋的结构类型// label < item> < label> Pucca< / label> < value> 01< / value> < / item> < item> < label> Semi pucca< / label> < value> 02< / value> < / item> < item> < label> Kutcha< / label> < value> 03< / value> < / item> < / select1> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / ownership_status> < label>所有权状态< / label> < item> < label>自有房屋< / label> < value> 01< / value> < / item> < item> < label>租户< / label> < value> 02< / value> < / item> < item> < label>不提供信息< / label> < value> 03< / value> < / item> < / select1> < input ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / no_of_family_fembers> < label>家庭成员人数< / label> < / input> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / do_you_have_girl_child_child_under_a> < label>您是否有18岁以下的女童？< / label> < item> < label"是< / label> < value> 01< / value> < / item> < item> < label>否< / label> < value> 02< / value> < / item> < / select1> < input ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / if_yes_how_many> < label>如果是，多少？ < / input> < input ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / house_area_in_sq_ft> < label>房屋面积ft< / label> < / input> < select ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / current_place_of_defecation_t> < label>当前排便的地方（厕所）< label> < item> < label>个人厕所< / label> < value> 01< / value> < / item> < item> < label>社区厕所（CTB）< < value> 02< / value> < / item> < item> < label>共用厕所< / label> < value> 03< / value> < / item> < item> < label>开放空间< / label> < value> 04< / value> < / item> < item> < label>公共厕所（PTB）< label" < value> 05< / value> < / item> < / select> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / where_the_individual_toilet_is> < label>个人洗手间连接到的地方？< / label> < item> < label>直接连接到下水道线< / label> < value> 01< / value> < / item> < item> < label>单坑厕所< / label> < value> 02< / value> < / item> < item> < label>双坑厕所< / label> < value> 03< / value> < / item> < item> < label>单个化粪池< / label> < value> 04< / value> < / item> < item> < label>个别沼气/ label < value> 05< / value> < / item> < item> < label> Biotoilet< / label> < value> 06< / value> < / item> < item> < label>非功能性座便器< / label> < value> 07< / value> < / item> < item> < label>不知道< / label> < value> 09< / value> < / item> < / select1> < select ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / type_of_water_connection> < label>水连接的类型< / label> < item> < label>个人连接< / label> < value> 01< / value> < / item> < item> < label>共享连接< / label> < value> 02< / value> < / item> < item> < label> Water standpost< / label> < value> 03< / value> < / item> < item> < label> Handpump< / label> < value> 04< / value> < / item> < item> < label>储水罐< / label> < value> 05< / value> < / item> < item> < label> Well< / label> < value> 06< / value> < / item> < / select> < select ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / facility_of_waste_collection> < label>废物收集设施< / label> < item> < label> MLA赞助的节奏// label < value> 01< / value> < / item> < item> < label>门到门废物收集< / label> < value> 02< / value> < / item> < item> < label> ULB ghantagadi< / label> < value> 03< / value> < / item> < item> < label> ULB van< / label> < value> 04< / value> < / item> < item> < label>垃圾桶< / label> < value> 05< / value> < / item> < item> < label>开放空间< / label> < value> 06< / value> < / item> < item> < label>沿着/内侧的运河< / label> < value> 07< / value> < / item> < item> < label>内部装订线< / label> < value> 08< / value> < / item> < / select> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / interested_in_individual_toile> < label>对个人洗手间感兴趣吗？< / label> < item> < label"是< / label> < value> 01< / value> < / item> < item> < label>否< / label> < value> 02< / value> < / item> < / select1> < select ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / if_yes_why> < label>如果是，为什么？< / label> < item> < label>为了女性家庭成员的安全< / label> < value> 01< / value> < / item> < item> < label>对CTB< / label>不满意< value> 02< / value> < / item> < item> < label>为了更好的方便< / label> < value> 03< / value> < / item> < item> < label>针对老年人< / label> < value> 04< / value> < / item> < item> < label>对于残障人士< / label> < value> 05< / value> < / item> < item> < label>对于患有疾病的任何成员< / label> < value> 06< / value> < / item> < item> < label>为了更好的健康和卫生< / label> < value> 07< / value> < / item> < item> < label>其他< / label> < value> 08< / value> < / item> < / select> < select ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / if_no_why> < label>如果否，为什么？< / label> < item> < label>财务问题< / label> < value> 01< / value> < / item> < item> < label>小房子< / label> < value> 02< / value> < / item> < item> < label>租户问题< / label> < value> 03< / value> < / item> < item> < label>缺乏意愿< / label> < value> 04< / value> < / item> < item>对CTB< / label>满意的< label> < value> 05< / value> < / item> < item> < label>大家庭人数< / label> < value> 06< / value> < / item> < item> < label>与排水有关的问题< / label> < value> 07< / value> < / item> < item> < label>其他< / label> < value> 08< / value> < / item> < / select> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / type_of_toilet_preference> < label>厕所偏好的类型< / label> < item> < label> Prefab< / label> < value> 01< / value> < / item> < item> < label>门口的材料< / label> < value> 02< / value> < / item> < item> < label> Any< / label> < value> 03< / value> < / item> < item> < label>不知道< / label> < value> 04< / value> < / item> < / select1> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / have_you_applied_or_individual> < label>您是否已根据SBM申请或单独上过厕所？ < item> < label"是< / label> < value> 01< / value> < / item> < item> < label>否< / label> < value> 02< / value> < / item> < / select1> < select1 ref = / uploaded_form_usq3zf / group_xb9nq26 / group_io6zh11 / how_many_installments_have_yo> < label>您已收到多少笔分期付款？< / label> < item> < label> 0< / label> < value> 01< / value> < / item> < item> < label> 1< / label> < value> 02< / value> < / item> < item> < label> 2< / label> < value> 03< / value> < / item> < / select1> < / group> < / group> < / body> < / html>我正在尝试以以下格式获取数据：
问题标签值 ----------------------------- ---------------------------------------- select_administrative_ward_nam Digha 09 Name_of_surveyor_s_who_collec_001 Amol Amol Name_of_surveyor_s_who_collec_001 Dhananjay dananjay type_of_structure_of_the_house Pucca 01 type_of_structure_of $ b我正在尝试以这种格式获取数据。
基本上所有一列下的问题都说列名是'Questions'，所有标签在一栏下，一栏下的所有值都说栏名是值。
这可能吗？任何帮助都将非常有用...
解决方案尝试一下：
，表1为（选择$$< html> < head> < title> Sanjay Gandhi Nagar RHS Navi Mumbai< / title> < model> < instance> < uploaded_form_usq3zf id = Sanjay_Gandhi_Nagar_RHS_Navi_M> < formhub> < uuid /> < ; / formhub> < group_jg6ts57> < city_name> 7149< / city_name> < select_administrative_ward_nam> 09< / select_administrative_ward_nam> < select; ; < date_of_rapid_household_survey /> < Name_of_surveyor_s_w ho_collec_001 /> < house_number /> < / group_jg6ts57> < type_of_house_occupancy /> < group_xb9nq26> < group_do8xg48> < what_is_the_full_name_of_the_f /> < mobile_number /> < aadhar_card_number /> < / group_do8xg48> < group_io6zh11> < type_of_structure_of_the_house /> < ownership_status /> < no_of_family_fembers /> < do_you_have_girl_child_child_under_a /> < if_yes_how_many /> < house_area_in_sq_ft /> < current_place_of_defecation_t /> < where_the_individual_toilet_is /> < type_of_water_connection /> < facility_of_waste_collection /> < interested_in_individual_toile /> < if_yes_why /> < if_no_why /> < type_of_toilet_preference /> < have_you_applied_or_individual /> < how_many_installments_have_yo /> < / group_io6zh11> < / group_xb9nq26> < start /> < end /> < meta> < instanceID /> < / meta> < / uploaded_form_usq3zf> < / instance> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / city_name required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / select_administrative_ward_nam required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / select_slum required = true（） type = select1 /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / date_of_rapid_household_survey required = true（） type = date /> < bind nodeset = / uploaded_form_usq3zf / group_jg6ts57 / Name_of_surveyor_s_who_collec_001 required = true（） type = select1 /> <bind constraint=\". <= 9999\" nodeset=\"/uploaded_form_usq3zf/group_jg6ts57/house_number\" required=\"true()\" type=\"int\"/> <bind nodeset=\"/uploaded_form_usq3zf/type_of_house_occupancy\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/what_is_the_full_name_of_the_f\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ or /uploaded_form_usq3zf/type_of_house_occupancy = ’03’\" required=\"true()\" type=\"string\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/mobile_number\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"string\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/aadhar_card_number\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"false()\" type=\"string\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_structure_of_the_house\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/ownership_status\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/no_of_family_fembers\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"int\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_how_many\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a = ’01’\" required=\"true()\" type=\"int\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/house_area_in_sq_ft\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"int\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"select\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/where_the_individual_toilet_is\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ and selected( /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t , ’01’)\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_water_connection\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"select\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/facility_of_waste_collection\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"select\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ and not(selected( /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t , ’01’))\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_why\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = ’01’\" required=\"true()\" type=\"select\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_no_why\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = ’02’\" required=\"true()\" type=\"select\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_toilet_preference\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = ’01’\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’\" required=\"true()\" type=\"select1\"/> <bind nodeset=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/how_many_installments_have_yo\" relevant=\" /uploaded_form_usq3zf/type_of_house_occupancy = ’01’ and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual = ’01’\" required=\"true()\" type=\"select1\"/> <bind preload=\"timestamp\" preloadParams=\"start\" nodeset=\"/uploaded_form_usq3zf/start\" type=\"dateTime\"/> <bind preload=\"timestamp\" preloadParams=\"end\" nodeset=\"/uploaded_form_usq3zf/end\" type=\"dateTime\"/> <bind calculate=\"concat(’uuid:’, uuid())\" nodeset=\"/uploaded_form_usq3zf/meta/instanceID\" readonly=\"true()\" type=\"string\"/> <bind calculate=\"’1c0b0cef39054d85bdf2b17bb17e4043’\" nodeset=\"/uploaded_form_usq3zf/formhub/uuid\" type=\"string\"/> </model> </head> <body> <group appearance=\"field-list\" ref=\"/uploaded_form_usq3zf/group_jg6ts57\"> <label>Administration Information</label> <select1 ref=\"/uploaded_form_usq3zf/group_jg6ts57/city_name\"> <label>City Name</label> <item> <label>Navi Mumbai</label> <value>7149</value> </item> </select1> <select1 ref=\"/uploaded_form_usq3zf/group_jg6ts57/select_administrative_ward_nam\"> <label>Select Administrative Ward Name</label> <item> <label>Digha</label> <value>09</value> </item> </select1> <select1 ref=\"/uploaded_form_usq3zf/group_jg6ts57/select_slum\"> <label>Select Slum</label> <item> <label>Sanjay Gandhi Nagar</label> <value>272171490901</value> </item> </select1> <input ref=\"/uploaded_form_usq3zf/group_jg6ts57/date_of_rapid_household_survey\"> <label>Date of Rapid Household Survey</label> </input> <select1 ref=\"/uploaded_form_usq3zf/group_jg6ts57/Name_of_surveyor_s_who_collec_001\"> <label>Name of surveyor(s) who collected Rapid Household Survey data</label> <item> <label>Amol</label> <value>amol</value> </item> <item> <label>Dhananjay</label> <value>dhananjay</value> </item> <item> <label>Reshma</label> <value>reshma</value> </item> <item> <label>Shankar</label> <value>shankar</value> </item> <item> <label>Sheetal</label> <value>sheetal</value> </item> <item> <label>Vaibhav</label> <value>vaibhav</value> </item> </select1> <input ref=\"/uploaded_form_usq3zf/group_jg6ts57/house_number\"> <label>House Number</label> </input> </group> <select1 ref=\"/uploaded_form_usq3zf/type_of_house_occupancy\"> <label>Type of Structure Occupancy</label> <item> <label>Occupied</label> <value>01</value> </item> <item> <label>Locked house</label> <value>02</value> </item> <item> <label>Double house</label> <value>03</value> </item> <item> <label>Broken house</label> <value>04</value> </item> <item> <label>House under construction</label> <value>05</value> </item> <item> <label>Permanent locked</label> <value>06</value> </item> <item> <label>Not giving information</label> <value>07</value> </item> </select1> <group ref=\"/uploaded_form_usq3zf/group_xb9nq26\"> <label>Household Information</label> <group appearance=\"field-list\" ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48\"> <label>Personal Information</label> <input ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/what_is_the_full_name_of_the_f\"> <label>What is the full name of the family head?</label> </input> <input ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/mobile_number\"> <label>Mobile Number</label> </input> <input ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/aadhar_card_number\"> <label>Aadhar Card Number</label> </input> </group> <group ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11\"> <label>General Information</label> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_structure_of_the_house\"> <label>Type of structure of the house</label> <item> <label>Pucca</label> <value>01</value> </item> <item> <label>Semi pucca</label> <value>02</value> </item> <item> <label>Kutcha</label> <value>03</value> </item> </select1> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/ownership_status\"> <label>Ownership status</label> <item> <label>Own house</label> <value>01</value> </item> <item> <label>Tenants</label> <value>02</value> </item> <item> <label>Not giving information</label> <value>03</value> </item> </select1> <input ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/no_of_family_fembers\"> <label>No of Family Members</label> </input> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a\"> <label>Do you have girl child under age 18 ?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <input ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_how_many\"> <label>If yes, how many?</label> </input> <input ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/house_area_in_sq_ft\"> <label>House Area in Sq. ft</label> </input> <select ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t\"> <label>Current place of defecation (toilet)</label> <item> <label>Individual toilet</label> <value>01</value> </item> <item> <label>Community toilet block (CTB)</label> <value>02</value> </item> <item> <label>Shared toilet</label> <value>03</value> </item> <item> <label>Open space</label> <value>04</value> </item> <item> <label>Public toilet (PTB)</label> <value>05</value> </item> </select> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/where_the_individual_toilet_is\"> <label>Where the Individual toilet is connected to?</label> <item> <label>Direct to sewer line</label> <value>01</value> </item> <item> <label>Single pit latrine</label> <value>02</value> </item> <item> <label>Twin pit latrine</label> <value>03</value> </item> <item> <label>Individual septic tank</label> <value>04</value> </item> <item> <label>Individual biogas</label> <value>05</value> </item> <item> <label>Biotoilet</label> <value>06</value> </item> <item> <label>Non-functional toilet</label> <value>07</value> </item> <item> <label>Don’t know</label> <value>09</value> </item> </select1> <select ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_water_connection\"> <label>Type of water connection</label> <item> <label>Individual connection</label> <value>01</value> </item> <item> <label>Shared connection</label> <value>02</value> </item> <item> <label>Water standpost</label> <value>03</value> </item> <item> <label>Handpump</label> <value>04</value> </item> <item> <label>Water tanker</label> <value>05</value> </item> <item> <label>Well</label> <value>06</value> </item> </select> <select ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/facility_of_waste_collection\"> <label>Facility of Waste Collection</label> <item> <label>MLA sponsored tempo</label> <value>01</value> </item> <item> <label>Door to door waste collection</label> <value>02</value> </item> <item> <label>ULB ghantagadi</label> <value>03</value> </item> <item> <label>ULB van</label> <value>04</value> </item> <item> <label>Garbage bin</label> <value>05</value> </item> <item> <label>Open space</label> <value>06</value> </item> <item> <label>Along/Inside canal</label> <value>07</value> </item> <item> <label>Inside gutter</label> <value>08</value> </item> </select> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile\"> <label>Interested in Individual toilet?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <select ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_why\"> <label>If YES, why?</label> <item> <label>For safety of female family members</label> <value>01</value> </item> <item> <label>Unsatisfied with CTB</label> <value>02</value> </item> <item> <label>For better convenience</label> <value>03</value> </item> <item> <label>For elderly</label> <value>04</value> </item> <item> <label>For handicapped</label> <value>05</value> </item> <item> <label>For any member suffering from illness</label> <value>06</value> </item> <item> <label>For better health and hygiene</label> <value>07</value> </item> <item> <label>Other</label> <value>08</value> </item> </select> <select ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_no_why\"> <label>If NO, why?</label> <item> <label>Financial problems</label> <value>01</value> </item> <item> <label>Small house</label> <value>02</value> </item> <item> <label>Tenant issue</label> <value>03</value> </item> <item> <label>Lack of willingness</label> <value>04</value> </item> <item> <label>Satisfied with the CTB</label> <value>05</value> </item> <item> <label>Large family size</label> <value>06</value> </item> <item> <label>Drainage related issues</label> <value>07</value> </item> <item> <label>Others</label> <value>08</value> </item> </select> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_toilet_preference\"> <label>Type of Toilet Preference</label> <item> <label>Prefab</label> <value>01</value> </item> <item> <label>Material at doorstep</label> <value>02</value> </item> <item> <label>Any</label> <value>03</value> </item> <item> <label>Don’t know</label> <value>04</value> </item> </select1> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual\"> <label>Have you applied or individual toilet under SBM ?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <select1 ref=\"/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/how_many_installments_have_yo\"> <label>How many instalments have you received ?</label> <item> <label>0</label> <value>01</value> </item> <item> <label>1</label> <value>02</value> </item> <item> <label>2</label> <value>03</value> </item> </select1> </group> </group> </body> </html>$$::xml xml_content) select ref_select_tab[array_length(ref_select_tab,1)] \"Questions\",(xpath(’//label/text()’,one_item))[1] as \"Label\",(xpath(’//value/text()’,one_item))[1] as \"Value\" from ( select string_to_array((xpath(’@ref’,one_select))[1]::character varying,’/’) ref_select_tab,unnest(xpath(’//item’,one_select)) one_item from ( select unnest(xpath(’//select1’,xml_content)) one_select from table1 ) a ) b
I have this xml content:
<html> <head> <title>Sanjay Gandhi Nagar RHS Navi Mumbai</title> <model> <instance> <uploaded_form_usq3zf id="Sanjay_Gandhi_Nagar_RHS_Navi_M"> <formhub> <uuid/> </formhub> <group_jg6ts57> <city_name>7149</city_name> <select_administrative_ward_nam>09</select_administrative_ward_nam> <select_slum>272171490901</select_slum> <date_of_rapid_household_survey/> <Name_of_surveyor_s_who_collec_001/> <house_number/> </group_jg6ts57> <type_of_house_occupancy/> <group_xb9nq26> <group_do8xg48> <what_is_the_full_name_of_the_f/> <mobile_number/> <aadhar_card_number/> </group_do8xg48> <group_io6zh11> <type_of_structure_of_the_house/> <ownership_status/> <no_of_family_fembers/> <do_you_have_girl_child_under_a/> <if_yes_how_many/> <house_area_in_sq_ft/> <current_place_of_defecation_t/> <where_the_individual_toilet_is/> <type_of_water_connection/> <facility_of_waste_collection/> <interested_in_individual_toile/> <if_yes_why/> <if_no_why/> <type_of_toilet_preference/> <have_you_applied_or_individual/> <how_many_installments_have_yo/> </group_io6zh11> </group_xb9nq26> <start/> <end/> <meta> <instanceID/> </meta> </uploaded_form_usq3zf> </instance> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/city_name" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/select_administrative_ward_nam" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/select_slum" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/date_of_rapid_household_survey" required="true()" type="date"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/Name_of_surveyor_s_who_collec_001" required="true()" type="select1"/> <bind constraint=". <= 9999" nodeset="/uploaded_form_usq3zf/group_jg6ts57/house_number" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/type_of_house_occupancy" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/what_is_the_full_name_of_the_f" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' or /uploaded_form_usq3zf/type_of_house_occupancy = '03'" required="true()" type="string"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/mobile_number" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="string"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/aadhar_card_number" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="false()" type="string"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_structure_of_the_house" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/ownership_status" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/no_of_family_fembers" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_how_many" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a = '01'" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/house_area_in_sq_ft" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/where_the_individual_toilet_is" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and selected( /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t , '01')" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_water_connection" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/facility_of_waste_collection" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and not(selected( /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t , '01'))" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_why" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_no_why" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = '02'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_toilet_preference" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/how_many_installments_have_yo" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual = '01'" required="true()" type="select1"/> <bind preload="timestamp" preloadParams="start" nodeset="/uploaded_form_usq3zf/start" type="dateTime"/> <bind preload="timestamp" preloadParams="end" nodeset="/uploaded_form_usq3zf/end" type="dateTime"/> <bind calculate="concat('uuid:', uuid())" nodeset="/uploaded_form_usq3zf/meta/instanceID" readonly="true()" type="string"/> <bind calculate="'1c0b0cef39054d85bdf2b17bb17e4043'" nodeset="/uploaded_form_usq3zf/formhub/uuid" type="string"/> </model> </head> <body> <group appearance="field-list" ref="/uploaded_form_usq3zf/group_jg6ts57"> <label>Administration Information</label> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/city_name"> <label>City Name</label> <item> <label>Navi Mumbai</label> <value>7149</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/select_administrative_ward_nam"> <label>Select Administrative Ward Name</label> <item> <label>Digha</label> <value>09</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/select_slum"> <label>Select Slum</label> <item> <label>Sanjay Gandhi Nagar</label> <value>272171490901</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_jg6ts57/date_of_rapid_household_survey"> <label>Date of Rapid Household Survey</label> </input> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/Name_of_surveyor_s_who_collec_001"> <label>Name of surveyor(s) who collected Rapid Household Survey data</label> <item> <label>Amol</label> <value>amol</value> </item> <item> <label>Dhananjay</label> <value>dhananjay</value> </item> <item> <label>Reshma</label> <value>reshma</value> </item> <item> <label>Shankar</label> <value>shankar</value> </item> <item> <label>Sheetal</label> <value>sheetal</value> </item> <item> <label>Vaibhav</label> <value>vaibhav</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_jg6ts57/house_number"> <label>House Number</label> </input> </group> <select1 ref="/uploaded_form_usq3zf/type_of_house_occupancy"> <label>Type of Structure Occupancy</label> <item> <label>Occupied</label> <value>01</value> </item> <item> <label>Locked house</label> <value>02</value> </item> <item> <label>Double house</label> <value>03</value> </item> <item> <label>Broken house</label> <value>04</value> </item> <item> <label>House under construction</label> <value>05</value> </item> <item> <label>Permanent locked</label> <value>06</value> </item> <item> <label>Not giving information</label> <value>07</value> </item> </select1> <group ref="/uploaded_form_usq3zf/group_xb9nq26"> <label>Household Information</label> <group appearance="field-list" ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48"> <label>Personal Information</label> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/what_is_the_full_name_of_the_f"> <label>What is the full name of the family head?</label> </input> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/mobile_number"> <label>Mobile Number</label> </input> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/aadhar_card_number"> <label>Aadhar Card Number</label> </input> </group> <group ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11"> <label>General Information</label> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_structure_of_the_house"> <label>Type of structure of the house</label> <item> <label>Pucca</label> <value>01</value> </item> <item> <label>Semi pucca</label> <value>02</value> </item> <item> <label>Kutcha</label> <value>03</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/ownership_status"> <label>Ownership status</label> <item> <label>Own house</label> <value>01</value> </item> <item> <label>Tenants</label> <value>02</value> </item> <item> <label>Not giving information</label> <value>03</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/no_of_family_fembers"> <label>No of Family Members</label> </input> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a"> <label>Do you have girl child under age 18 ?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_how_many"> <label>If yes, how many?</label> </input> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/house_area_in_sq_ft"> <label>House Area in Sq. ft</label> </input> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t"> <label>Current place of defecation (toilet)</label> <item> <label>Individual toilet</label> <value>01</value> </item> <item> <label>Community toilet block (CTB)</label> <value>02</value> </item> <item> <label>Shared toilet</label> <value>03</value> </item> <item> <label>Open space</label> <value>04</value> </item> <item> <label>Public toilet (PTB)</label> <value>05</value> </item> </select> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/where_the_individual_toilet_is"> <label>Where the Individual toilet is connected to?</label> <item> <label>Direct to sewer line</label> <value>01</value> </item> <item> <label>Single pit latrine</label> <value>02</value> </item> <item> <label>Twin pit latrine</label> <value>03</value> </item> <item> <label>Individual septic tank</label> <value>04</value> </item> <item> <label>Individual biogas</label> <value>05</value> </item> <item> <label>Biotoilet</label> <value>06</value> </item> <item> <label>Non-functional toilet</label> <value>07</value> </item> <item> <label>Don't know</label> <value>09</value> </item> </select1> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_water_connection"> <label>Type of water connection</label> <item> <label>Individual connection</label> <value>01</value> </item> <item> <label>Shared connection</label> <value>02</value> </item> <item> <label>Water standpost</label> <value>03</value> </item> <item> <label>Handpump</label> <value>04</value> </item> <item> <label>Water tanker</label> <value>05</value> </item> <item> <label>Well</label> <value>06</value> </item> </select> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/facility_of_waste_collection"> <label>Facility of Waste Collection</label> <item> <label>MLA sponsored tempo</label> <value>01</value> </item> <item> <label>Door to door waste collection</label> <value>02</value> </item> <item> <label>ULB ghantagadi</label> <value>03</value> </item> <item> <label>ULB van</label> <value>04</value> </item> <item> <label>Garbage bin</label> <value>05</value> </item> <item> <label>Open space</label> <value>06</value> </item> <item> <label>Along/Inside canal</label> <value>07</value> </item> <item> <label>Inside gutter</label> <value>08</value> </item> </select> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile"> <label>Interested in Individual toilet?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_why"> <label>If YES, why?</label> <item> <label>For safety of female family members</label> <value>01</value> </item> <item> <label>Unsatisfied with CTB</label> <value>02</value> </item> <item> <label>For better convenience</label> <value>03</value> </item> <item> <label>For elderly</label> <value>04</value> </item> <item> <label>For handicapped</label> <value>05</value> </item> <item> <label>For any member suffering from illness</label> <value>06</value> </item> <item> <label>For better health and hygiene</label> <value>07</value> </item> <item> <label>Other</label> <value>08</value> </item> </select> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_no_why"> <label>If NO, why?</label> <item> <label>Financial problems</label> <value>01</value> </item> <item> <label>Small house</label> <value>02</value> </item> <item> <label>Tenant issue</label> <value>03</value> </item> <item> <label>Lack of willingness</label> <value>04</value> </item> <item> <label>Satisfied with the CTB</label> <value>05</value> </item> <item> <label>Large family size</label> <value>06</value> </item> <item> <label>Drainage related issues</label> <value>07</value> </item> <item> <label>Others</label> <value>08</value> </item> </select> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_toilet_preference"> <label>Type of Toilet Preference</label> <item> <label>Prefab</label> <value>01</value> </item> <item> <label>Material at doorstep</label> <value>02</value> </item> <item> <label>Any</label> <value>03</value> </item> <item> <label>Don't know</label> <value>04</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual"> <label>Have you applied or individual toilet under SBM ?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/how_many_installments_have_yo"> <label>How many instalments have you received ?</label> <item> <label>0</label> <value>01</value> </item> <item> <label>1</label> <value>02</value> </item> <item> <label>2</label> <value>03</value> </item> </select1> </group> </group> </body> </html>I am trying to get data in form following format:
Questions Label Value --------------------------------------------------------------------- select_administrative_ward_nam Digha 09 Name_of_surveyor_s_who_collec_001 Amol Amol Name_of_surveyor_s_who_collec_001 Dhananjay dananjay type_of_structure_of_the_house Pucca 01 type_of_structure_of_the_house Semi pucca 02I am trying to get the data in this format.
Basically all the questions under one column say columns name is , 'Questions' , all the labels under one column and all the values under one column say columns name is 'values'.
Is that possible to do? Any help will be great...
解决方案try this :
with table1 as (select $$<html> <head> <title>Sanjay Gandhi Nagar RHS Navi Mumbai</title> <model> <instance> <uploaded_form_usq3zf id="Sanjay_Gandhi_Nagar_RHS_Navi_M"> <formhub> <uuid/> </formhub> <group_jg6ts57> <city_name>7149</city_name> <select_administrative_ward_nam>09</select_administrative_ward_nam> <select_slum>272171490901</select_slum> <date_of_rapid_household_survey/> <Name_of_surveyor_s_who_collec_001/> <house_number/> </group_jg6ts57> <type_of_house_occupancy/> <group_xb9nq26> <group_do8xg48> <what_is_the_full_name_of_the_f/> <mobile_number/> <aadhar_card_number/> </group_do8xg48> <group_io6zh11> <type_of_structure_of_the_house/> <ownership_status/> <no_of_family_fembers/> <do_you_have_girl_child_under_a/> <if_yes_how_many/> <house_area_in_sq_ft/> <current_place_of_defecation_t/> <where_the_individual_toilet_is/> <type_of_water_connection/> <facility_of_waste_collection/> <interested_in_individual_toile/> <if_yes_why/> <if_no_why/> <type_of_toilet_preference/> <have_you_applied_or_individual/> <how_many_installments_have_yo/> </group_io6zh11> </group_xb9nq26> <start/> <end/> <meta> <instanceID/> </meta> </uploaded_form_usq3zf> </instance> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/city_name" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/select_administrative_ward_nam" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/select_slum" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/date_of_rapid_household_survey" required="true()" type="date"/> <bind nodeset="/uploaded_form_usq3zf/group_jg6ts57/Name_of_surveyor_s_who_collec_001" required="true()" type="select1"/> <bind constraint=". <= 9999" nodeset="/uploaded_form_usq3zf/group_jg6ts57/house_number" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/type_of_house_occupancy" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/what_is_the_full_name_of_the_f" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' or /uploaded_form_usq3zf/type_of_house_occupancy = '03'" required="true()" type="string"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/mobile_number" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="string"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/aadhar_card_number" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="false()" type="string"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_structure_of_the_house" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/ownership_status" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/no_of_family_fembers" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_how_many" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a = '01'" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/house_area_in_sq_ft" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="int"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/where_the_individual_toilet_is" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and selected( /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t , '01')" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_water_connection" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/facility_of_waste_collection" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and not(selected( /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t , '01'))" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_why" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = '01'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_no_why" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = '02'" required="true()" type="select"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_toilet_preference" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01'" required="true()" type="select1"/> <bind nodeset="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/how_many_installments_have_yo" relevant=" /uploaded_form_usq3zf/type_of_house_occupancy = '01' and /uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual = '01'" required="true()" type="select1"/> <bind preload="timestamp" preloadParams="start" nodeset="/uploaded_form_usq3zf/start" type="dateTime"/> <bind preload="timestamp" preloadParams="end" nodeset="/uploaded_form_usq3zf/end" type="dateTime"/> <bind calculate="concat('uuid:', uuid())" nodeset="/uploaded_form_usq3zf/meta/instanceID" readonly="true()" type="string"/> <bind calculate="'1c0b0cef39054d85bdf2b17bb17e4043'" nodeset="/uploaded_form_usq3zf/formhub/uuid" type="string"/> </model> </head> <body> <group appearance="field-list" ref="/uploaded_form_usq3zf/group_jg6ts57"> <label>Administration Information</label> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/city_name"> <label>City Name</label> <item> <label>Navi Mumbai</label> <value>7149</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/select_administrative_ward_nam"> <label>Select Administrative Ward Name</label> <item> <label>Digha</label> <value>09</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/select_slum"> <label>Select Slum</label> <item> <label>Sanjay Gandhi Nagar</label> <value>272171490901</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_jg6ts57/date_of_rapid_household_survey"> <label>Date of Rapid Household Survey</label> </input> <select1 ref="/uploaded_form_usq3zf/group_jg6ts57/Name_of_surveyor_s_who_collec_001"> <label>Name of surveyor(s) who collected Rapid Household Survey data</label> <item> <label>Amol</label> <value>amol</value> </item> <item> <label>Dhananjay</label> <value>dhananjay</value> </item> <item> <label>Reshma</label> <value>reshma</value> </item> <item> <label>Shankar</label> <value>shankar</value> </item> <item> <label>Sheetal</label> <value>sheetal</value> </item> <item> <label>Vaibhav</label> <value>vaibhav</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_jg6ts57/house_number"> <label>House Number</label> </input> </group> <select1 ref="/uploaded_form_usq3zf/type_of_house_occupancy"> <label>Type of Structure Occupancy</label> <item> <label>Occupied</label> <value>01</value> </item> <item> <label>Locked house</label> <value>02</value> </item> <item> <label>Double house</label> <value>03</value> </item> <item> <label>Broken house</label> <value>04</value> </item> <item> <label>House under construction</label> <value>05</value> </item> <item> <label>Permanent locked</label> <value>06</value> </item> <item> <label>Not giving information</label> <value>07</value> </item> </select1> <group ref="/uploaded_form_usq3zf/group_xb9nq26"> <label>Household Information</label> <group appearance="field-list" ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48"> <label>Personal Information</label> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/what_is_the_full_name_of_the_f"> <label>What is the full name of the family head?</label> </input> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/mobile_number"> <label>Mobile Number</label> </input> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_do8xg48/aadhar_card_number"> <label>Aadhar Card Number</label> </input> </group> <group ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11"> <label>General Information</label> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_structure_of_the_house"> <label>Type of structure of the house</label> <item> <label>Pucca</label> <value>01</value> </item> <item> <label>Semi pucca</label> <value>02</value> </item> <item> <label>Kutcha</label> <value>03</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/ownership_status"> <label>Ownership status</label> <item> <label>Own house</label> <value>01</value> </item> <item> <label>Tenants</label> <value>02</value> </item> <item> <label>Not giving information</label> <value>03</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/no_of_family_fembers"> <label>No of Family Members</label> </input> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/do_you_have_girl_child_under_a"> <label>Do you have girl child under age 18 ?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_how_many"> <label>If yes, how many?</label> </input> <input ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/house_area_in_sq_ft"> <label>House Area in Sq. ft</label> </input> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/current_place_of_defecation_t"> <label>Current place of defecation (toilet)</label> <item> <label>Individual toilet</label> <value>01</value> </item> <item> <label>Community toilet block (CTB)</label> <value>02</value> </item> <item> <label>Shared toilet</label> <value>03</value> </item> <item> <label>Open space</label> <value>04</value> </item> <item> <label>Public toilet (PTB)</label> <value>05</value> </item> </select> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/where_the_individual_toilet_is"> <label>Where the Individual toilet is connected to?</label> <item> <label>Direct to sewer line</label> <value>01</value> </item> <item> <label>Single pit latrine</label> <value>02</value> </item> <item> <label>Twin pit latrine</label> <value>03</value> </item> <item> <label>Individual septic tank</label> <value>04</value> </item> <item> <label>Individual biogas</label> <value>05</value> </item> <item> <label>Biotoilet</label> <value>06</value> </item> <item> <label>Non-functional toilet</label> <value>07</value> </item> <item> <label>Don't know</label> <value>09</value> </item> </select1> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_water_connection"> <label>Type of water connection</label> <item> <label>Individual connection</label> <value>01</value> </item> <item> <label>Shared connection</label> <value>02</value> </item> <item> <label>Water standpost</label> <value>03</value> </item> <item> <label>Handpump</label> <value>04</value> </item> <item> <label>Water tanker</label> <value>05</value> </item> <item> <label>Well</label> <value>06</value> </item> </select> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/facility_of_waste_collection"> <label>Facility of Waste Collection</label> <item> <label>MLA sponsored tempo</label> <value>01</value> </item> <item> <label>Door to door waste collection</label> <value>02</value> </item> <item> <label>ULB ghantagadi</label> <value>03</value> </item> <item> <label>ULB van</label> <value>04</value> </item> <item> <label>Garbage bin</label> <value>05</value> </item> <item> <label>Open space</label> <value>06</value> </item> <item> <label>Along/Inside canal</label> <value>07</value> </item> <item> <label>Inside gutter</label> <value>08</value> </item> </select> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/interested_in_individual_toile"> <label>Interested in Individual toilet?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_yes_why"> <label>If YES, why?</label> <item> <label>For safety of female family members</label> <value>01</value> </item> <item> <label>Unsatisfied with CTB</label> <value>02</value> </item> <item> <label>For better convenience</label> <value>03</value> </item> <item> <label>For elderly</label> <value>04</value> </item> <item> <label>For handicapped</label> <value>05</value> </item> <item> <label>For any member suffering from illness</label> <value>06</value> </item> <item> <label>For better health and hygiene</label> <value>07</value> </item> <item> <label>Other</label> <value>08</value> </item> </select> <select ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/if_no_why"> <label>If NO, why?</label> <item> <label>Financial problems</label> <value>01</value> </item> <item> <label>Small house</label> <value>02</value> </item> <item> <label>Tenant issue</label> <value>03</value> </item> <item> <label>Lack of willingness</label> <value>04</value> </item> <item> <label>Satisfied with the CTB</label> <value>05</value> </item> <item> <label>Large family size</label> <value>06</value> </item> <item> <label>Drainage related issues</label> <value>07</value> </item> <item> <label>Others</label> <value>08</value> </item> </select> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/type_of_toilet_preference"> <label>Type of Toilet Preference</label> <item> <label>Prefab</label> <value>01</value> </item> <item> <label>Material at doorstep</label> <value>02</value> </item> <item> <label>Any</label> <value>03</value> </item> <item> <label>Don't know</label> <value>04</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/have_you_applied_or_individual"> <label>Have you applied or individual toilet under SBM ?</label> <item> <label>Yes</label> <value>01</value> </item> <item> <label>No</label> <value>02</value> </item> </select1> <select1 ref="/uploaded_form_usq3zf/group_xb9nq26/group_io6zh11/how_many_installments_have_yo"> <label>How many instalments have you received ?</label> <item> <label>0</label> <value>01</value> </item> <item> <label>1</label> <value>02</value> </item> <item> <label>2</label> <value>03</value> </item> </select1> </group> </group> </body> </html>$$::xml xml_content) select ref_select_tab[array_length(ref_select_tab,1)] "Questions",(xpath('//label/text()',one_item))[1] as "Label",(xpath('//value/text()',one_item))[1] as "Value" from ( select string_to_array((xpath('@ref',one_select))[1]::character varying,'/') ref_select_tab,unnest(xpath('//item',one_select)) one_item from ( select unnest(xpath('//select1',xml_content)) one_select from table1 ) a ) b
更多推荐
在postgres中以表格格式从xml内容获取数据
发布评论