Oracle XMLQuery

http://psoug.org/reference/xmlquery.html

General Note: XMLQuery returns query results as XML. XMLTable returns results as relation data.   Demo Tables CREATE TABLE person_data ( person_id   NUMBER(3), person_data XMLTYPE); Demo Data INSERT INTO person_data (person_id, person_data) VALUES (1, XMLTYPE('  <PDRecord>    <PDName>Daniel Morgan</PDName>    <PDDOB>12/1/1951</PDDOB>    <PDEmail>damorgan@u.washington.edu</PDEmail>  </PDRecord>') ); INSERT INTO person_data (person_id, person_data) VALUES (2, XMLTYPE('  <PDRecord>    <PDName>Jack Cline</PDName>    <PDDOB>5/17/1949</PDDOB>    <PDEmail>damorgan@u.washington.edu</PDEmail>  </PDRecord>') ); INSERT INTO person_data (person_id, person_data) VALUES (3, XMLTYPE('  <PDRecord>    <PDName>Caleb Small</PDName>    <PDDOB>1/1/1960</PDDOB>    <PDEmail>damorgan@u.washington.edu</PDEmail>  </PDRecord>') ); COMMIT; col person_data format a50 SELECT * FROM person_data; set long 100000 SELECT * FROM person_data;   SELECT Simple Query (with equals) SELECT <column_list>, XMLQuery ( 'for $i IN <record_end_tag>  where $i<item_end_tag> = <value>  order by $i<item_end_tag>  return $i<item_end_tag> PASSING BY VALUE <xml_record_column> RETURNING CONTENTS) <returning_column_alias> FROM <table_name>; Note: What is within the parentheses is case sensitive and you can not use Upper Case or InitCap for commands. SELECT person_id, XMLQuery( 'for $i in /PDRecord  where $i /PDName = "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName eq "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; Greater Than SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName > "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName gt "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; Greater Than Or Equal To SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName >= "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName ge "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; Less Than SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName < "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName lt "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; Less Than Or Equal To SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName >= "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName le "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; Not Equals SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName != "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; SELECT person_id, XMLQuery ( 'for $i in /PDRecord  where $i /PDName ne "Daniel Morgan"  order by $i/PDName  return $i/PDName' passing by value person_data RETURNING CONTENT) XMLData FROM person_data; Additional Syntax Elements [ // div union <CastAs> - >> * intersect <CastableAs> + and idiv   <EOF> | or mod   <InstanceOf> except is to   <TreatAs>

Related Topics DBMS_XMLGEN XML Functions XMLTable XML Tables

转载于:https://www.cnblogs.com/hailiang/archive/2012/05/19/2508711.html