XML - Cast type via XMLTYPE avec XMLAGG - définir nom balise

**raynox** · 05/11/2018, 20h19

Bonjour,

Je rencontre quelques soucis, pour convertir une table en XML

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SELECT XMLSERIALIZE(document  XMLElement("Request",
    xmlelement("Element",
            xmlelement("TimeStamp",NULL),
            xmlelement("TaxesTimeStamp",NULL),
            xmlelement("CmpCode",NULL),
            xmlelement("Level",NULL),
            xmlelement("Code",NULL),
            xmlelement("Name",NULL),
            xmlelement("ShortName",NULL),
            xmlelement("Matchable",NULL),
            xmlelement("CustomerSupplier",NULL),
            xmlelement("SubAnalyse",NULL),
            xmlelement("AccountType",NULL),
            xmlelement("CurrencyCode",NULL),
            xmlelement("StartPeriod",NULL),
            xmlelement("EndPeriod",NULL),
            xmlelement("CompulsoryDescr",NULL),
            xmlelement("KeepTurnover",NULL),
            xmlelement("PayStatus",NULL),
            xmlelement("EnablePay",NULL),
            xmlelement("Summary",NULL),
            xmlelement("Split",NULL),
            xmlelement("Settlement",NULL),
            xmlelement("ForceDisperse",NULL),
            xmlelement("PaperMedia",NULL),
            xmlelement("ElecMedia",NULL),
            xmlelement("DefaultMedia",NULL),
            xmlelement("Terms",NULL),
            xmlelement("TaxAdjustment",NULL),
            xmlelement("DiscountEnable",NULL),
            xmlelement("TaxMethod",NULL),
            xmlelement("DateAccountOpened",NULL),
            xmlelement("ElementStatus",NULL),
            xmlelement("AccountSummary",NULL),
            xmlelement("MemoStatus",NULL),
            xmlelement("VAT",NULL),
            xmlelement("TaxRepESL",NULL),
            xmlelement("EUVATCode",NULL),
            xmlelement("ReportingCode1",NULL),
            xmlelement("ReportingCode2",NULL),
            xmlelement("ReportingCode3",NULL),
            xmlelement("SIC",NULL),
            xmlelement("IndirectCode",NULL),
            xmlelement("Ten99",NULL),
            xmlelement("Ten99Code",NULL),
            xmlelement("FederalTax",NULL),
            xmlelement("SocialSecurity",NULL),
            xmlelement("SecondTIN",NULL),
            xmlelement("User",NULL),
            xmlelement("Addresses",(SELECT  
                                xmlagg(
                                    xmlelement("Address", 
                                                     xmlelement("Tag", ADDR.tag),
                                                     xmlelement("Name", ADDR.name)
                                                )
                                        )
 
                              FROM OAS_ELMADDRLIST ADDR
                              WHERE ADDR.CMPCODE = ELM.CMPCODE AND
                              ADDR.ELMCODE = ELM.CODE AND
                              ADDR.ELMLEVEL = ELM.ELMLEVEL)
                    )
                )
            ) AS CLOB INDENT SIZE = 6)
  AS deptxml
        FROM OAS_ELEMENT ELM WHERE CODE LIKE 'F0%' AND ELMLEVEL = 2

Cet exemple fonctionne ci-dessous, mais je ne suis pas pleinement satisfait, car si la valeur n'est pas définie pour la balise celle-ci est quand même affichée.

L'autre solution est d'utiliser cette solution (avec des types) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
CREATE OR REPLACE TYPE xml_element AS OBJECT (
....
);
 
CREATE OR REPLACE TYPE xml_address AS OBJECT (
                    "Tag" VARCHAR2(300),
                    "Name" VARCHAR2(300)
                    );

Ce qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SELECT XMLSERIALIZE(document  XMLElement("Request",
    xml_element(
                      0,                              -- TimeStamp
                      0,                              -- TaxesTimeStamp
                      ELM.CMPCODE,                    -- CmpCode
                      ELM.ELMLEVEL,                   -- Level
                      ELM.CODE,                       -- Code
                      ELM.NAME,                       -- Name
                      ELM.SNAME,                      -- ShortName
                      NULL,                              -- Matchable
                      NULL,                              -- CustomerSupplier
                      NULL,                              -- SubAnalyse
                      NULL,                              -- AccountType
                      ELM.CUR,                        -- CurrencyCode
                      NULL,                              -- StartPeriod
                      NULL,                              -- EndPeriod
                      NULL,                              -- CompulsoryDescr
                      NULL,                              -- KeepTurnover
                      NULL,                              -- PayStatus
                      NULL,                              -- EnablePay
                      NULL,                              -- Summary
                      NULL,                              -- Split
                      NULL,                              -- Settlement
                      NULL,                              -- ForceDisperse
                      NULL,                              -- PaperMedia
                      NULL,                              -- ElecMedia
                      NULL,                              -- DefaultMedia
                      NULL,                              -- Terms
                      NULL,                              -- TaxAdjustment
                      NULL,                              -- DiscountEnable
                      NULL,                              -- TaxMethod
                      NULL,                              -- DateAccountOpened
                      NULL,                              -- ElementStatus
                      NULL,                              -- AccountSummary
                      NULL,                              -- MemoStatus
                      NULL,                              -- VAT
                      NULL,                              -- TaxRepESL
                      NULL,                              -- EUVATCode
                      NULL,                              -- ReportingCode1
                      NULL,                              -- ReportingCode2
                      NULL,                              -- ReportingCode3
                      NULL,                              -- SIC
                      NULL,                              -- IndirectCode
                      NULL,                              -- Ten99
                      NULL,                              -- Ten99Code
                      NULL,                              -- FederalTax
                      NULL,                              -- SocialSecurity
                      NULL,                              -- SecondTIN
                      'INFORMATIQUE',                    -- User
                                          (SELECT  
                                xmlagg(
 
                                                     XMLTYPE((xml_address(0,0)))
 
                                        )
 
                              FROM OAS_ELMADDRLIST ADDR
                              WHERE ADDR.CMPCODE = ELM.CMPCODE AND
                              ADDR.ELMCODE = ELM.CODE AND
                              ADDR.ELMLEVEL = ELM.ELMLEVEL
                              ),                              -- Addresses
                      NULL,                              -- Banks
                      NULL,                              -- Groups
                      NULL,                              -- Medias
                      NULL                               -- Comments
      ) AS "Element"
                )  AS CLOB INDENT SIZE = 6)
  AS deptxml
        FROM OAS_ELEMENT ELM WHERE CODE LIKE 'F0%' AND ELMLEVEL = 2

Losqu'il constitue la balise de la partie xml_address, il la nomme "xml_address" ce qui ne me convient pas puisqu'elle devrait se nommer "Address" (comme le 1er exemple plus haut) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
...
            <Addresses>
                  <XML_ADDRESS>
                        <Tag>0</Tag>
                        <Name>0</Name>
                  </XML_ADDRESS>
                  <XML_ADDRESS>
                        <Tag>0</Tag>
                        <Name>0</Name>
                  </XML_ADDRESS>
            </Addresses>
...

Donc je fais comme pour le xml_element (AS "Element"), j'ajoute donc un AS "Address" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 XMLTYPE((xml_address(0,0) AS "Address"))

et la dans ce cas, ça ne fonctionne pas, il me dit qu'il y a un problème de parenthèse droite manquante, juste en ajoutant AS "Address", voici la requête avec la modification

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
ORA-00907: parenthèse de droite absente
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Erreur à la ligne 59, colonne 82

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
  SELECT XMLSERIALIZE(document  XMLElement("Request",
      xml_element(
                        0,                              -- TimeStamp
                        0,                              -- TaxesTimeStamp
                        ELM.CMPCODE,                    -- CmpCode
                        ELM.ELMLEVEL,                   -- Level
                        ELM.CODE,                       -- Code
                        ELM.NAME,                       -- Name
                        ELM.SNAME,                      -- ShortName
                        NULL,                              -- Matchable
                        NULL,                              -- CustomerSupplier
                        NULL,                              -- SubAnalyse
                        NULL,                              -- AccountType
                        ELM.CUR,                        -- CurrencyCode
                        NULL,                              -- StartPeriod
                        NULL,                              -- EndPeriod
                        NULL,                              -- CompulsoryDescr
                        NULL,                              -- KeepTurnover
                        NULL,                              -- PayStatus
                        NULL,                              -- EnablePay
                        NULL,                              -- Summary
                        NULL,                              -- Split
                        NULL,                              -- Settlement
                        NULL,                              -- ForceDisperse
                        NULL,                              -- PaperMedia
                        NULL,                              -- ElecMedia
                        NULL,                              -- DefaultMedia
                        NULL,                              -- Terms
                        NULL,                              -- TaxAdjustment
                        NULL,                              -- DiscountEnable
                        NULL,                              -- TaxMethod
                        NULL,                              -- DateAccountOpened
                        NULL,                              -- ElementStatus
                        NULL,                              -- AccountSummary
                        NULL,                              -- MemoStatus
                        NULL,                              -- VAT
                        NULL,                              -- TaxRepESL
                        NULL,                              -- EUVATCode
                        NULL,                              -- ReportingCode1
                        NULL,                              -- ReportingCode2
                        NULL,                              -- ReportingCode3
                        NULL,                              -- SIC
                        NULL,                              -- IndirectCode
                        NULL,                              -- Ten99
                        NULL,                              -- Ten99Code
                        NULL,                              -- FederalTax
                        NULL,                              -- SocialSecurity
                        NULL,                              -- SecondTIN
                        'INFORMATIQUE',                    -- User
                                            (SELECT  
                                  xmlagg(
 
                                                       XMLTYPE((xml_address(0,0) AS "Address"))
 
                                          )
 
                                FROM OAS_ELMADDRLIST ADDR
                                WHERE ADDR.CMPCODE = ELM.CMPCODE AND
                                ADDR.ELMCODE = ELM.CODE AND
                                ADDR.ELMLEVEL = ELM.ELMLEVEL
                                ),                              -- Addresses
                        NULL,                              -- Banks
                        NULL,                              -- Groups
                        NULL,                              -- Medias
                        NULL                               -- Comments
        ) AS "Element"
                  )  AS CLOB INDENT SIZE = 6)
    AS deptxml
          FROM OAS_ELEMENT ELM WHERE CODE LIKE 'F0%' AND ELMLEVEL = 2

Y a t-il une solution pour nommer ma balise "Address" ?

Je viens de trouver ça comme idée mais un peu bricoleur :p meilleure idée ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

XMLTYPE(replace (XMLTYPE(xml_address(0,0)).getClobVal(), 'XML_ADDRESS', 'Address'))

OU

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

XMLTYPE( REGEXP_REPLACE(XMLTYPE(xml_address(0,0)).getClobVal(), '^<[^>]+>(.+)<\/[^>]+>$', '<Address>\1</Address>'))

OU

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

xmltype('<Address>'||REGEXP_REPLACE(XMLTYPE(xml_address(0,0)).getClobVal(), '^<[^>]+>(.+)<\/[^>]+>$', '\1')||'</Address>')

Merci par avance

**mnitu** · 08/11/2018, 11h31

Il y a plein des exemples dans la doc XML sur le site d'Oracle. Ensuite si vous voulez plus d'aide postez un jeu d'essaie (création table, insert ...) avec vos données et vos besoins.

**tsuji** · 03/01/2019, 12h34

J'ai regardé la problématique soulevée et la structure de sortie désirée. Je pense qu'on peut tout à fait rester dans la fonctionnalité fournie par sql/xml, celle concrétisée dans la première solution. Pourtant il faut réécrire la partie concernée pour en profitant la spécialité de la fonction xmlforest().

Dans l'original, c'est écrit comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
    SELECT XMLSERIALIZE(document
        XMLElement("Request",
            xmlelement("Element",
                xmlelement("TimeStamp",NULL),
                xmlelement("TaxesTimeStamp",NULL),
                -- etc etc
                xmlelement("Addresses", 
                    (SELECT  
                        xmlagg(
                            xmlelement("Address", 
                                xmlelement("Tag", ADDR.tag),
                                xmlelement("Name", ADDR.name)
                            )
                        )
                    FROM OAS_ELMADDRLIST ADDR
                    WHERE ADDR.CMPCODE = ELM.CMPCODE AND
                    ADDR.ELMCODE = ELM.CODE AND
                    ADDR.ELMLEVEL = ELM.ELMLEVEL)
                )
            )
        ) AS CLOB INDENT SIZE = 6
    ) AS deptxml
    FROM OAS_ELEMENT ELM WHERE CODE LIKE 'F0%' AND ELMLEVEL = 2

Pour obtenir les résultats souhaîtés dans les cas où ADDR soient absents, on peut la réécrire comme ça.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
    SELECT xmlserialize(document
        xmlelement("Request",
            xmlelement("Element",
                xmlelement("TimeStamp",NULL),
                xmlelement("TaxesTimeStamp",NULL),
                -- etc etc
                xmlforest( 
                    (SELECT  
                        xmlagg(
                            xmlforest( 
                                ADDR.tag as "Tag",
                                ADDR.name as "Name"
                            ) as "Address"
                        )
                    FROM OAS_ELMADDRLIST ADDR
                    WHERE ADDR.CMPCODE = ELM.CMPCODE AND
                    ADDR.ELMCODE = ELM.CODE AND
                    ADDR.ELMLEVEL = ELM.ELMLEVEL)
                ) as "Addresses"
            )
        ) AS CLOB INDENT SIZE = 6
    ) AS deptxml
    FROM OAS_ELEMENT ELM WHERE CODE LIKE 'F0%' AND ELMLEVEL = 2

Essayez-le pour voir si ça a réussit. Je le réécris mentalement et je peux me tromper dans le détail; mais ce serait comme ça dans la grosse conception.

XML - Cast type via XMLTYPE avec XMLAGG - définir nom balise

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager