6 Replies Latest reply on Sep 1, 2013 8:34 AM by odie_63

    ora-19102 Xquery string literally expected

    1029527

      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'

      )

        • 1. Re: ora-19102 Xquery string literally expected
          1029527

          anybody please help me its very urgent

          • 2. Re: ora-19102 Xquery string literally expected
            1029527

            hello guru's  please guide me

            • 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.

              1 person found this helpful
              • 4. Re: ora-19102 Xquery string literally expected
                1029527

                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