Getting XML out of a table created in SQL

Question

I have created this table in Oracle SQL Developer

CREATE TABLE Test_T (
  COL_1 VARCHAR(30),
  COL_2 XMLTYPE
);

And have inserted this into it

INSERT INTO Test_T VALUES ('two', ('<?xml version="1.0" encoding="UTF-8"?>
<CATALOG>
 <PLANT>
  <COMMON>Bloodroot</COMMON>
  <BOTANICAL>Sanguinaria canadensis</BOTANICAL>
  <ZONE>4</ZONE>
  <LIGHT>Mostly Shady</LIGHT>
  <PRICE>$2.44</PRICE>
  <AVAILABILITY>031599</AVAILABILITY>
 </PLANT>
 <PLANT>
  <COMMON>Columbine</COMMON>
  <BOTANICAL>Aquilegia canadensis</BOTANICAL>
  <ZONE>3</ZONE>
  <LIGHT>Mostly Shady</LIGHT>
  <PRICE>$9.37</PRICE>
  <AVAILABILITY>030699</AVAILABILITY>
 </PLANT></CATALOG>'));

My goal is to return the <COMMON> Name </COMMON> ONLY WHERE the zone is 3 or LESS. So this should return Columbine.

I thought about using XMLExists Im not too familiar with XML so this is what I had so far.

SELECT COL_2 FROM Test_T WHERE XMLExists('//ZONE[COL_2 <= 3]' PASSING BY REF COL_2);

I'm not sure if I am accessing the ZONE right.

Could anyone guide me in the right direction?

What DBMS are you using?

eftpotrm
– eftpotrm

2016-04-05 15:51:48 +00:00
Commented Apr 5, 2016 at 15:51 — eftpotrm
– eftpotrm, Commented Apr 5, 2016 at 15:51
@eftpotrm, So sorry. Using Oracle SQL Developer

impact_Sv
– impact_Sv

2016-04-05 15:55:04 +00:00
Commented Apr 5, 2016 at 15:55 — impact_Sv
– impact_Sv, Commented Apr 5, 2016 at 15:55

SomeDude · Accepted Answer · 2016-04-05 20:18:21Z

1

Try the below select query :

 SELECT COMMON_NAME FROM Test_T WHERE XMLExists( 'CATALOG/PLANT[ZONE<=3]/COMMON[text()]' PASSING COMMON_NAME )

answered Apr 5, 2016 at 20:18

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel · Accepted Answer · 2016-04-05 20:44:22Z

The problem is with your path( '//ZONE[COL_2 <= 3]' ). COL_2 is not a valid XML node, it's just the name of your column.

The proper path would be //ZONE[text() <= 3].

text() is a special node reference that tells oracle to grab the text inside the ZONE node <ZONE>THIS TEXT</ZONE>. You can only target nodes in your actual XML schema.

Also, be aware that the path is CASE SENSITIVE to what's in your XML. Remembering that will save you time.

Additionally, another way to write your select would be this. In this example, Oracle does an implicit join and returns a row for each //PLANT with /ZONE/text() <= 3. The path in the XMLSEQUENCE is important here because it determines how oracle splits up each row, meaning that you can't just target //ZONE because you would only get a row for each ZONE rather than a row for each PLANT.

In the select clause, you can extract individual node values for each PLANT if you have more than one.

SELECT VALUE(P) --THE FULL XML FOR EACH PLANT
    VALUE(P).EXTRACT('//COMMON/text()').getstringval() AS COMMON, --INDIVIDUAL NODE VALUE
    VALUE(P).EXTRACT('//BOTANICAL/text()').getstringval() AS BOTANICAL --INDIVIDUAL NODE VALUE
FROM Test_T, TABLE(XMLSEQUENCE(EXTRACT(COL_2, '//PLANT[ZONE<= 3]'))) p

Collectives™ on Stack Overflow

Getting XML out of a table created in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related