XMLtable avec Oracle 11g

Voici un exemple de table:

create table xmltemp (mydoc xmltype)

Voici un petit document xml:

insert into xmltemp values (
xmltype
('<?xml version="1.0"?>
<countries>
  <country>
    <name>Canada</name>
  </country>
  <country>
    <name>US</name>
    <states>
      <state>
        <name>Washington</name>
        <name>Oregon</name>        
      </state>
    </states>
  </country>
</countries>
')
)

Avis que le Canada n'a pas d '"états" de l'élément, mais le NOUS ne.
Je vais essayer d'obtenir ces résultats de la requête (l'ordre et la mise en forme n'est pas important):

Canada,
US,Washington
US,Oregon

Lorsque j'exécute ça, je vois à la fois le Canada et les états-unis dans le résultat:

select
countryname
from xmltemp,
xmltable('/countries/country' passing mydoc
   columns countryname varchar2(10) path 'name')

Quand je fais cela, je reçois à la fois aux états-unis:

select
statename
from xmltemp,
xmltable('/countries/country/states/state/name' passing mydoc
   columns statename   varchar2(20) path '.') c

J'ai essayé d'obtenir les deux pays et les états, mais il semble que oracle n'aime pas le '..' syntaxe:

select
statename
from xmltemp,
xmltable('/countries/country/states/state/name' passing mydoc
   columns statename   varchar2(20) path '.',
           countryname varchar2(20) path '../../../name') c

Voici l'erreur:

ORA-19110: unsupported XQuery expression

Quand j'ai essayer ceci, j'obtiens le "multi-élément' erreur parce que deux états:

select
countryname,
statename
from xmltemp,
xmltable('/countries/country' passing mydoc
   columns countryname varchar2(10) path 'name',
           statename   varchar2(20) path 'states/state/name') c

Ici est que l'erreur:

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton 
sequence - got multi-item sequence

Ce qui est une requête qui va me faire mon choix de sortie:

Canada,
US,Washington
US,Oregon

Grâce

OriginalL'auteur John | 2012-02-17

  1. 4

    Essayez ceci:

    select      X.COUNTRYNAME, Y.STATENAME
from        XMLTEMP
           ,xmltable('/countries/country'
                     passing MYDOC
                     columns COUNTRYNAME varchar2(20) path './name', 
                             STATES xmltype path './states') X,
            xmltable('/states/state/name' passing X.STATES 
                    columns STATENAME varchar2(20) path '.') (+) Y

    Parce que vous avez plusieurs états, vous devez joindre à un autre xml de la table. Que certains pays n'ont pas d'états, alors il doit être une jointure externe gauche. Je suis en utilisant l'ancienne méthode de (+) que je suis en train d'essayer cela sur 10g et il semble qu'il ya un problème à l'aide de left outer join dans 10g mais apparemment, ça devrait être bon dans 11g.

    Merci. J'ai considéré l'outer-join hier, mais ne pouvait pas tout à fait le comprendre. Bien qu'ils ne sont pas spécifiques à Oracle, j'ai trouvé ces deux liens utiles: [Partie 1] (ibm.com/developerworks/data/library/techarticle/dm-0708nicola) [Partie 2] (ibm.com/developerworks/data/library/techarticle/dm-0709nicola)
    Cela semble être la bonne réponse que je ne trouve pas que 11g prend en charge un parent d'adressage dans n'importe quelle forme dans le CHEMIN de la clause. Fondamentalement, ce qui est fait ici est que le parent enregistrements sont chargés dans un XMLTABLE avec l'enfant les éléments contenus à l'intérieur d'un XMLTYPE colonne de ce résultat. Puis, à l'aide de la XMLTYPE enfant de la colonne des enregistrements parent, de charger des enregistrements de l'enfant dans un autre XMLTABLE. Ensuite Oracle implicitement se joint à toutes les tables. --- Ce que je viens de trouver, cependant, est que les 11g ne pas les jointures externes correctement avec juste un (+), je dois mettre le (+) sur x et y tables, qui ne fait pas beaucoup de sens.
    Autre bizarrerie, j'ai trouvé avec 11g, c'est que je ne peut pas traiter les éléments par attribut spécifique, comme prévu lors de l'utilisation d'une valeur par défaut xmlnamespace. La solution de contournement est de l'adresse de l'attribut avec le caractère générique de l'espace de noms (de manière explicite de l'espace de noms ne fonctionne pas non plus). Par exemple, ne fonctionne pas: CHEMIN d'accès 'ID[@type="user"]'. Ne fonctionne pas: CHEMIN d'accès 'ID[@toto:type="user"]'. Œuvres: CHEMIN d'accès 'ID[@*:type="user"]'.

    OriginalL'auteur John Doyle