6 Replies Latest reply: Sep 1, 2013 3:34 AM by odie_63 RSS

    ora-19102 Xquery string literally expected

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

      Hi all ,

       

      When iam running the below query i am getting the error ora-19102 Xquery string literally expected.

       

      Anbody can u please help me in this .

       

      select StyleNumber, LaRedouteNumber, Option_Style

      from xml_laredoutenumbers xtab,

             xmltable(XMLNamespaces ('http://www.collage.com/'  ),

             '/LaRedouteNumbers'

              passing xtab.LaRedouteNumbers

      columns     STYLENUMBER             varchar2(25) path 'Stylelist/Style/StyleNumber'

              ,   LAREDOUTENUMBER         varchar2(25) path 'Stylelist/Style/LaRedouteNumber'

              ,   OPTION_STYLE            varchar2(10) path 'Stylelist/Style/Option_Style'

      )

        • 3. Re: ora-19102 Xquery string literally expected
          Jason_(A_Non)

          This is a forum made up of volunteers who answer questions as their time permits.

           

          Without setting up code to run your query, I can see that the XMLNamespaces clause is wrong.  See

          How do I declare namespace prefix mapping with XMLTable() ?

          for an example of what a valid construct looks like.

          • 4. Re: ora-19102 Xquery string literally expected
            47e033bd-d313-47bd-9372-871358ce3c3e

            Hi Thanks ,

             

            I need some more information my XMl looks like below.

             

            <?xml version="1.0" encoding="UTF-8"?>

            <ns1:LaRedouteNumbers xmlns:ns1="http://www.cool.com/">

              <StyleList>

                <Style>

                  <StyleNumber>76624087</StyleNumber>

                  <LaRedouteNumber>8575313</LaRedouteNumber>

                  <Option_Style>

                    <Option>

                      <SKU_Option>

                        <SKU>

                          <SKUNumber>76624162</SKUNumber>

                          <LaRedouteNumber>5952411</LaRedouteNumber>

                        </SKU>

                        <SKU>

                          <SKUNumber>76624131</SKUNumber>

                          <LaRedouteNumber>1828158</LaRedouteNumber>

                        </SKU>

                        <SKU>

                          <SKUNumber>76624094</SKUNumber>

                          <LaRedouteNumber>7311914</LaRedouteNumber>

                        </SKU>

              </SKU_Option>

                    </Option>

                  </Option_Style>

                </Style>

              </StyleList>

            </ns1:LaRedouteNumbers>

             

             

             

            In this i want the output like below,

            StyleNumber LaRedouteNumber2 SKUNumber LaRedouteNumber

            76624087     8575313                     76624162    5952411

            76624087     8575313                     76624131    1828158

            76624087     8575313                     76624094     7311914.

             

            As simple i want to get the column StyleNumber  and LaRedouteNumber2  how many times the SKUNumber  and LaRedouteNumber is there currently am using the below query.

             

            select stylenumber, laredoutenumber, skunumber

            from xml_laredoutenumbers xtab,

                   xmltable(XMLNamespaces ('http://www.cool.com/' as "a"  ),

                   'a:LaRedouteNumbers/StyleList/Style/Option_Style/Option/SKU_Option/SKU'

                    passing xtab.xml_data

            columns     STYLENUMBER             varchar2(25) path 'a:LaRedouteNumbers/StyleList/Style/StyleNumber'

                    ,   LAREDOUTENUMBER         varchar2(25) path 'LaRedouteNumber'

                   ,   skunumber            varchar2(10) path 'SKUNumber'

            ) x1;

             

             

            Please anybody helo me

            • 5. Re: ora-19102 Xquery string literally expected
              Jason_(A_Non)

              With XMLTable, you cannot go up node levels (or see outside where the XPath picks), based on how XMLTable works internally.  Just the way it is.  In order to accomplish what you need, which is a classic parent/child relationship, you simply need to extract the child nodes for each parent and then process those child nodes in a separate XMLTable statement.  Yes, you only have one parent in your example, but the concept still applies regardless of how many parents or children there are.  What you are looking for is something like

              select stylenumber, laredoutenumber2, x2.skunumber, x2.laredoutenumber
              from xml_laredoutenumbers xtab,
                     xmltable(XMLNamespaces ('http://www.cool.com/' as "a"  ),
                     'a:LaRedouteNumbers/StyleList/Style'
                      passing xtab.xml_data
                      columns     
                          STYLENUMBER             varchar2(25) path 'StyleNumber'
                      ,   LAREDOUTENUMBER2        varchar2(25) path 'LaRedouteNumber'
                      ,   skuxml                  XMLType      path 'Option_Style/Option/SKU_Option/SKU'
                     ) x1,
                     xmltable(
                     '/SKU'
                      passing x1.skuxml
                      columns     
                          skunumber               varchar2(25) path 'SKUNumber'
                      ,   LAREDOUTENUMBER         varchar2(25) path 'LaRedouteNumber'
                     ) x2;
              

              x1 is used to grab the parent and as part of that it also grabs the repeating child nodes (as skuxml).  These child nodes are passed to x2, via the PASSING clause, and that maintains the parent/child relationship.  It allows x2 to parse each child and then the rows are joined together to create the result set.  You can include "skuxml" in your SELECT statement to see the XML that is passed from x1 to x2.

              • 6. Re: ora-19102 Xquery string literally expected
                odie_63

                With XMLTable, you cannot go up node levels (or see outside where the XPath picks), based on how XMLTable works internally.

                It's possible now on 12.1