1 2 Previous Next 29 Replies Latest reply on Aug 1, 2013 9:40 AM by 1029527 Go to original post
      • 15. Re: Mahesh
        odie_63

        This is the PurchaseOrder schema.

        The corresponding data certainly not resides in the EMPLOYEES table. How do you intend to generate a valid XML, from which table ?

        • 16. Re: Mahesh
          1029527

          yes you are correct but if i am giving the schema wrong it should through error right but its executing

          see the code below

          SELECT XMLElement("Employee",

          XMLAttributes('http://www.w3.org/2001/XMLSchema-instance'

          as "xmlns:xsi",'http://www.example.com/schemas/ipo.xsd'

          as "xsi:SchemaLocation")

                 , XMLForest(employee_id)

                 ) AS "RESULT"

          FROM employees

          WHERE department_id=10;

           

          in the above its all related to ipo.xsd but its executing and giving the result , if it is pointing to the wrong xsd it should through error but i am getting result for this

           

          Thanks,

          mahesh

          • 17. Re: Mahesh
            odie_63
            yes you are correct but if i am giving the schema wrong it should through error right but its executing

            Yes, sorry for the misunderstanding.

             

            The automatic validation will take place only if the schema location is specified properly.

            It's not the case in your example :

             

            - there's a typo on "xsi:SchemaLocation" : should be "xsi:schemaLocation".

            - the location itself is incorrect. When the schema defines a target namespace, the location must be given using "namespace_uri schema_url".

             

            This works for me on 10.2.0.5 (the closest I have to your version) :

            Connected to:

            Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

            With the Partitioning, OLAP, Data Mining and Real Application Testing options

             

            SQL> SELECT XMLElement("Dummy",

              2           XMLAttributes(

              3             'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"

              4           , 'http://www.example.com/IPO http://www.example.com/schemas/ipo.xsd' as "xsi:schemaLocation"

              5           )

              6         )

              7  FROM dual ;

            FROM dual

                 *

            ERROR at line 7:

            ORA-31043: Element 'Dummy' not globally defined in schema

            'http://www.example.com/schemas/ipo.xsd'

             

            • 18. Re: Mahesh
              1029527

              Hi thanks ,

               

              the same error which i am getting right now if this is the case can you provide me how to over come this error

              ORA-31043: Element 'Dummy' not globally defined in schema 'http://www.example.com/schemas/ipo.xsd'

               

              Mahesh

              • 19. Re: Mahesh
                odie_63

                That's not an "error" per se.

                It means the schema validation is now working as expected and has detected that the dummy document doesn't conform to the specified schema.

                Is it not what you wanted to check?

                • 20. Re: Mahesh
                  1029527

                  Hi thanks its working fine when i changed the DUMMY to purchaseOrder in the root element.

                   

                  SELECT XMLElement("purchaseOrder",

                  XMLAttributes('http://www.w3.org/2001/XMLSchema-instance' AS

                  "xmlns:xsi",

                  'http://www.example.com/IPO http://www.example.com/schemas/ipo.xsd' AS

                  "xsi:schemaLocation"),

                  XMLForest(employee_id, last_name, salary)) AS "RESULT"

                  FROM hr.employees

                  WHERE department_id = 10;

                   

                  But still my question is XSD is different and the passing values are different see it below

                  XMLForest(employee_id, last_name, salary)) AS "RESULT"

                  FROM hr.employees

                  WHERE department_id = 10;

                   

                  But still it is executing and giving output if iam right when i am referning to wrong XMLForest(employee_id, last_name, salary)) and wrong table also it should through the

                  error.

                   

                  Mahesh

                  • 21. Re: Mahesh
                    1029527

                    could you please provide me a best way to validate my generated xml with xsd

                    • 22. Re: Mahesh
                      odie_63

                      OK, after testing a little bit further myself, it looks like we cannot rely on the schema validation feature this way (at least on 10.2). It seems to only validate the root node.

                       

                      If you can afford a second step, you have to use XMLType's schemaValidate() method.

                      It'll perform a full validation and throw an error if the validation fails :

                      DECLARE

                       

                        doc   xmltype;

                       

                      BEGIN

                       

                        select xmlelement( ... )

                        into doc

                        from my_table ;

                       

                        doc.schemaValidate();

                       

                      END;

                      /

                      • 23. Re: Mahesh
                        1029527

                        Hi Thanks a lot thank you so much its working fine .

                         

                        I like to know one more thing in this doc   xmltype in this how much memory the doc will store.

                         

                        Because there is a possibilty for which the select statement can store millions of record , i would like to know two points clearly.

                        1.Is the doc can hold more memory is the number of rows retrived is more than million.

                        2.And if the select statement is selecting more rows which means it will be genereated as one XML?

                         

                        Pleas help me on this .

                         

                        Mahesh

                        • 24. Re: Mahesh
                          odie_63

                          1.Is the doc can hold more memory is the number of rows retrived is more than million.

                          In your version, the XMLType datatype is still based on a CLOB object, which can hold gigabytes of data.

                           

                          Let me ask you another question : what are you intending to do with the XML? Writing to file, sending to another Oracle process, etc. ?

                           

                          2.And if the select statement is selecting more rows which means it will be genereated as one XML?

                          Well, you have to make sure it returns only one row.

                          • 25. Re: Mahesh
                            1029527

                            Hi Thanks,

                             

                            1. I want to generate an XML and i have to validate it with XSD and then this XML file has to be send to a remote place.

                            2. And i also want to generate xml  for many rows as a one file and have to validate as well as,

                             

                            Thank you,

                            Mahesh

                            • 26. Re: Mahesh
                              1029527

                              if i want to store all as one xml and also xsd validation which would be the best way please suggest some ideas

                              • 27. Re: Mahesh
                                1029527

                                Hi Please share me some points its really very urgent.

                                • 28. Re: Mahesh
                                  odie_63

                                  47e033bd-d313-47bd-9372-871358ce3c3e wrote:

                                   

                                  if i want to store all as one xml and also xsd validation which would be the best way please suggest some ideas

                                  I already showed you how to do it.

                                   

                                  1. Generate your XML document from your base tables using SQL/XML functions (XMLElement, XMLAttributes, XMLAgg etc.)
                                  2. Validate the resulting XMLType using schemaValidate() method

                                   

                                  Which of those two points you don't understand?

                                  • 29. Re: Mahesh
                                    1029527

                                    hi thanks ya got it XMLAGG is working fine very thnaks again

                                    1 2 Previous Next