>我有一个xml数据列。XML 数据包含具有不同页眉的表单。每个页面都有无线电类型值。我需要从一个页面中选取值及其值。
我需要来自页面主题="外展"的值"id",问题值="外展类型",因为我需要 id="1" 值,还需要来自问题值="评估原因"的 id 值。
下面是示例 xml 数据。
<Test name="Assessment " header="" logoimg="" background="" icon="" firstPage="1" allow_abort="1" is_um="0" is_hidden="0" do_only="1" status="1" locale_id="0" version="74" category_id="150011" maxQuestionID="37" id="10234" force_start="0" user_name=" ggg bbb">
<PAGE topic="Outreach " progress="5" background="" headerid="" footerid="" flush_concepts="1" lastPage="false" id="1">
<BRANCH_CONDITION>
<NEXT_PAGE id="2" />
</BRANCH_CONDITION>
<QUESTION value="Type of Outreach " defaultvalue="" style="" tooltip="" read_only="0" usability="5" prefer_concept="0" type="radio" validation="0" id="1" pagename="Outreach ">
<OPTIONS>
<OPTION value="Outbound Telephone " id="1" url="" score="0" />
<OPTION value="Inbound Telephone" id="2" url="" score="0" />
<OPTION value="In Person" id="3" url="" score="0" />
</OPTIONS>
</QUESTION>
<QUESTION value="Reason for Assessment " defaultvalue="" style="" tooltip="" read_only="0" usability="5" prefer_concept="0" type="radio" validation="0" id="2" pagename="Outreach ">
<OPTIONS>
<OPTION value="New" id="1" url="" score="0" />
<OPTION value="Annual" id="2" url="" score="0" />
<OPTION value="Triggering Event " id="3" url="" score="0" />
<OPTION value="LOC Review" id="4" url="" score="0" />
</OPTIONS>
</QUESTION>
我尝试了以下sql。
SELECT TABLE.META_DATA.value('(/QUESTION//id/node())[1]','int') as outReach
,TABLE.META_DATA.value('(/QUESTION//id/node())[1]','int') as ReasonForAssessment
FROM Table
以下是我的预期结果
+----------+---------------------+
| Outreach | ReasonForAssessment |
+----------+---------------------+
| 1 | 2 |
几个问题:
-
QUESTION
不是 XML 的根,因此您必须使用//
或从根/Test/PAGE/QUESTION/
树向下走。 -
调整 XPath 以按带有前缀的属性搜索
@
而不是节点搜索,因为id
是QUESTION
的属性(不是子节点(。 -
调整节点索引
[#]
以返回相应的@id
属性值。
调整后的 SQL:
SELECT TABLE.META_DATA.value('(//QUESTION/@id)[1]','int') as outReach
, TABLE.META_DATA.value('(//QUESTION/@id)[2]','int') as ReasonForAssessment
FROM Table;
-- outReach ReasonForAssessment
-- 1 2
或者对列使用括号中的条件:
SELECT TABLE.META_DATA.value('(/Test/PAGE/QUESTION[@value=''Type of Outreach '']/@id)[1]','int') as outReach
, TABLE.META_DATA.value('(/Test/PAGE/QUESTION[@value=''Reason for Assessment '']/@id)[1]','int') as ReasonForAssessment
FROM Table;
-- outReach ReasonForAssessment
-- 1 2