oracle xmltable获取子节点 [英] oracle xmltable get children nodes
问题描述
<SRDBSW xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="String" ShortIds="Y" >
<OBJ_DF IdView="RW" ObjLevel="Element" Nature="" ObjectType="" ShortDescription="" ShortDescriptionCore="" LogicalShortDescription="" LongDescription="Reaction Wheel" LongDescriptionCore="" LogicalLongDescription="" Mnemonic="" IsDefined="Y" ModelType="" SerialNumber="" ProductTreeId="" CategoryFlag="7" OwnerFlag="7" InputVersion="" InputType="" InputReference="" >
<TC_STR_DF IdView="TCSTABCDE" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: TC Structure" Mnemonic="TC Structure" Type="TC_STR" >
<TC_STR_COMP_LIST>
<TC_STR_COMP CompOrder="3" ComponentType="Editable parameter" CompId="HABCA" EngValue="3" TakeValue="From occurrence" MonParRef="MBCDA" Mandatory="Yes" />
</TC_STR_COMP_LIST>
</TC_STR_DF>
</OBJ_DF>
我在Oracle表的xmltable中有XML的这一部分(这只是一个示例，我的XML充满了这些部分):
I have this section of XML in an xmltable in an Oracle table (this is just a sample, my XML is full with those sections):
<SCOS_TM_DF IdView="1111" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: SCOS TM Packet" Mnemonic="SCOS TM Packet" Type="SCOS_TM" TpcfName="My TM packet" InterpretFlag="Both" DefOffsetTime="66" >
<TM_STR_COMP_LIST>
<TM_STR_COMP ComponentType="Single structure" CompId="TMSTABCDE" CompBytePos="0" CompBitPos="0" TimeOffset="0" SelectorParRef="MABCA" SelRawValue="0" />
</TM_STR_COMP_LIST>
</SCOS_TM_DF>
我需要找到所有ComponentType="Single structure"，但还需要获取父亲" SCOS_TM_DF IdView="1111".
I need to find all ComponentType="Single structure", but also need to get the "father" SCOS_TM_DF IdView="1111".
到目前为止，我正在处理此查询，但无法获取idview:
So far, I'm working on this query, but can't get idview:
SELECT x.*
FROM xmlimport t
CROSS JOIN XMLTABLE ('/SRDBSW/OBJ_DF/TC_STR_DF/TC_STR_COMP_LIST/TC_STR_COMP/@*'
PASSING t.XMLDATA
COLUMNS
IdView VARCHAR2(30) PATH '/../../../@IdView',
CompId VARCHAR2(30) PATH '@CompId',
attr_name VARCHAR2(30) PATH 'local-name(.)',
attr_value VARCHAR2(90) PATH '.' ) x
WHERE t.xmlkey = 'SRDB-XML-sample-1.xml';
推荐答案
您还可以在XPath中构造自己的元素，该元素将基于父节点创建属性:
You can also construct your own element in the XPath, which creates an attribute based on the parent node:
SELECT x.*
FROM xmlimport t
CROSS JOIN XMLTABLE ('for $i in //*[@ComponentType = ''Single structure'']
return element {$i/name()} { attribute CompId {$i/@CompId},
attribute IdView {$i/../../@IdView},
$i }'
PASSING t.XMLDATA
COLUMNS IdView VARCHAR2(30) PATH '@IdView',
CompId VARCHAR2(30) PATH '@CompId',
attr_name VARCHAR2(30) PATH 'name(.)',
attr_value VARCHAR2(90) PATH '.'
) x
WHERE t.xmlkey = 'SRDB-XML-sample-1.xml';
我已经将节点名设为通用，因此它与所有ComponentType="Single structure"都匹配，就像您的问题说的那样.这意味着它与示例XML片段匹配.
I've made the node-name generic, so it matches all ComponentType="Single structure" as your question said you wanted; which means it matches the sample XML fragment.
由于您的示例节点没有任何内容，我已经猜到您想要attr_value是什么了.
I've sort of guessed what you want the attr_value to be, since your sample node didn't have any content.
带有扩展的片段:
<SRDBSW>
<OBJ_DF>
<SCOS_TM_DF IdView="1111" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: SCOS TM Packet" Mnemonic="SCOS TM Packet" Type="SCOS_TM" TpcfName="My TM packet" InterpretFlag="Both" DefOffsetTime="66" >
<TM_STR_COMP_LIST>
<TM_STR_COMP ComponentType="Single structure" CompId="TMSTABCDE" CompBytePos="0" CompBitPos="0" TimeOffset="0" SelectorParRef="MABCA" SelRawValue="0" />
</TM_STR_COMP_LIST>
</SCOS_TM_DF>
<TC_STR_DF IdView="1112" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: SCOS TM Packet" Mnemonic="SCOS TM Packet" Type="SCOS_TM" TpcfName="My TM packet" InterpretFlag="Both" DefOffsetTime="66" >
<TC_STR_COMP_LIST>
<TC_STR_COMP ComponentType="Single structure" CompId="TCSTABCDE" CompBytePos="0" CompBitPos="0" TimeOffset="0" SelectorParRef="MABCA" SelRawValue="0">Test value</TC_STR_COMP>
</TC_STR_COMP_LIST>
</TC_STR_DF>
</OBJ_DF>
</SRDBSW>
得到:
IDVIEW COMPID ATTR_NAME ATTR_VALUE
-------- --------------- -------------------- ---------------
1111 TMSTABCDE TM_STR_COMP
1112 TCSTABCDE TC_STR_COMP Test value
基于您现在需要IdView的元素名称的注释，您可以在生成的元素中将其作为附加属性来获取:
Based on your comment that you now want the element name that the IdView comes from, you can just grab that as an additional attribute in the generated element:
SELECT x.*
FROM xmlimport t
CROSS JOIN XMLTABLE (q'#for $i in //*[@ComponentType = 'Single structure']
return element {$i/name()} {attribute IdView {$i/../../@IdView},
attribute TopName {$i/../../name()},
$i }#'
PASSING t.XMLDATA COLUMNS IdView VARCHAR2(30) PATH '@IdView',
TopName VARCHAR2(30) PATH '@TopName',
attr_name VARCHAR2(30) PATH 'name(.)',
attr_value VARCHAR2(90) PATH '.'
) x
WHERE t.xmlkey = 'SRDB-XML-sample-1.xml';
IDVIEW TOPNAME ATTR_NAME ATTR_VALUE
-------- --------------- -------------------- ---------------
1111 SCOS_TM_DF TM_STR_COMP
1112 TC_STR_DF TC_STR_COMP Test value
我还改用了替代引号语法，这意味着您不必在Single structure值周围转义单引号.您只需要一个定界符，该定界符绝对不会出现在实际的字符串中，在本例中为XPath.通常，默认情况下，我个人通常会使用方括号，但是由于这些确实会出现在XPath中以进行属性匹配，因此我改用了#.
I've also switched to using the alternative quote syntax, which means you don't have to escape the single-quotes around the Single structure value. You just need a delimiter that definitely won't appear inside the actual string, the XPath in this case. I'd usually use square brackets by default, personally, but since those do appear in the XPath for the attribute match, I've used # instead.
这篇关于oracle xmltable获取子节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
