Tweet
Say for example I have this XML document, stored in an XML type column on an SQL table:
I want to test if the section1 tag exists.
I know how to use XMLTABLE to extract values. The problem is, I do not know which of section1's subtags may or may not exist. I could read them all and check if any have a value, but there are hundreds.
I think I have found a way, but I am very new to XPath so I do not know if this is the correct way to do it, I would really like confirmation.
If I use XMLTABLE to target a node as follows, it will return the concatenation of all values of all children/descendents:
If the node exists but has no value, and none of its children (if any) have value, the same XMLTABLE returns a zero-length string:
And finally, if the node does not exist, it returns null
So all I think I have do do is this, and test if chk is null, zero-length, or some other value.
Is this the right approach? or is there a better one?
Code:
<mydoc>
<section1>
<field1>value-11</field1>
<field2>value-12</field2>
</section1>
<section2>
<field1>value-21</field1>
<field2>value-22</field2>
</section2>
<section3>
<field1></field1>
</section3>
<section4></section4>
<section5/>
</mydoc>
I know how to use XMLTABLE to extract values. The problem is, I do not know which of section1's subtags may or may not exist. I could read them all and check if any have a value, but there are hundreds.
I think I have found a way, but I am very new to XPath so I do not know if this is the correct way to do it, I would really like confirmation.
If I use XMLTABLE to target a node as follows, it will return the concatenation of all values of all children/descendents:
Code:
select chk, length(chk) from xt2,
xmltable('/mydoc' passing xmlin columns
chk varchar(120) default null path 'section1'
) as chktable;
//chk = 'value-11value-12subvalue-11subvalue-12'
//length(chk) = 38
If the node exists but has no value, and none of its children (if any) have value, the same XMLTABLE returns a zero-length string:
Code:
select chk, length(chk) from xt2,
xmltable('/mydoc' passing xmlin columns
chk varchar(120) default null path 'section3'
) as chktable;
//chk = ''
//length(chk) = 0
// Same for section4 and section5
And finally, if the node does not exist, it returns null
Code:
select chk, length(chk) from xt2,
xmltable('/mydoc' passing xmlin columns
chk varchar(120) default null path 'section95'
) as chktable;
//chk = null
//length(chk) = null
// Same for section4 and section5
So all I think I have do do is this, and test if chk is null, zero-length, or some other value.
Is this the right approach? or is there a better one?
Comment