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