7 Replies Latest reply on Jul 15, 2019 3:03 AM by mNem

    Error parsing xml - Xquery dynamic type mismatch

    883125

      Hi,

      I have the below xml. Getting xquery dynamic type mismatch while using xmltable select.

      <ArrayOrdersDto xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.xyz">

        <OrdersDto>   

          <Price xmlns:d3p1="http://schemas.xyz" />

          <Suppliers>

            <SupplierDto>

              <SupplierId>0</SupplierId>

              <Price>32400</Price>       

            </SupplierDto>

            <SupplierDto>

              <SupplierId>1</SupplierId>

              <Price>32400</Price>

          </Suppliers>

          <Cities xmlns:d3p1="http://schemas.xyz">

            <d3p1:Cities>

              <d3p1:CitiesDto>         

                <d3p1:Id>21710035</d3p1:Id>         

              </d3p1:CitiesDto>

            </d3p1:Cities>

            <d3p1:Id>21464853</d3p1:Id>

            <d3p1:LastModifiedDate>0001-01-01T00:00:00</d3p1:LastModifiedDate>

          </Cities>

      <Id>32458</Id>

         </OrdersDto>

      </ArrayOrdersDto>

       

      Thanks.

        • 1. Re: Error parsing xml - Xquery dynamic type mismatch
          mNem

          Please post your query.

           

          The xml as is not well formed

           

                <SupplierDto>

                  <SupplierId>1</SupplierId>

                  <Price>32400</Price>

                </SupplierDto>

          • 2. Re: Error parsing xml - Xquery dynamic type mismatch
            883125

            Hi,

            I have the below xml. Getting xquery dynamic type mismatch while using xmltable select.

            <ArrayOrdersDto xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.xyz">

              <OrdersDto>  

                <Price xmlns:d3p1="http://schemas.xyz" />

                <Suppliers>

                  <SupplierDto>

                    <SupplierId>0</SupplierId>

                    <Price>32400</Price>      

                  </SupplierDto>

                  <SupplierDto>

                    <SupplierId>1</SupplierId>

                    <Price>32400</Price>

                </SupplierDto>

                </Suppliers>

                <Cities xmlns:d3p1="http://schemas.xyz">

                  <d3p1:Cities>

                    <d3p1:CitiesDto>        

                      <d3p1:Id>21710035</d3p1:Id>        

                    </d3p1:CitiesDto>

                  </d3p1:Cities>

                  <d3p1:Id>21464853</d3p1:Id>

                  <d3p1:LastModifiedDate>0001-01-01T00:00:00</d3p1:LastModifiedDate>

                </Cities>

            <Id>32458</Id>

               </OrdersDto>

            </ArrayOrdersDto>

             

            Thanks.

            • 3. Re: Error parsing xml - Xquery dynamic type mismatch
              tsuji

              Please post your query.

              Where did you post your query?

              • 4. Re: Error parsing xml - Xquery dynamic type mismatch
                883125

                select distinct

                             xt."SupplierId",          

                             xt."Id",

                             xt."Price"

                     -- bulk collect

                    --   into supplier_info

                         from (select dbf_remove_xml_namespace(xmltype(p_ret_data)) xmldata from dual) x,

                             xmltable('ArrayOrdersDto/OrdersDto' passing(x.xmldata) columns

                                      "SupplierId" number path 'SupplierDto/SupplierId',

                                      "Id" number path 'Cities/CitiesDto/Id',  "Price" number path 'Price'

                                      ) xt;

                • 5. Re: Error parsing xml - Xquery dynamic type mismatch
                  mNem

                  883125 wrote:

                   

                  select distinct

                  xt."SupplierId",

                  xt."Id",

                  xt."Price"

                  -- bulk collect

                  -- into supplier_info

                  from (select dbf_remove_xml_namespace(xmltype(p_ret_data)) xmldata from dual) x,

                  xmltable('ArrayOrdersDto/OrdersDto' passing(x.xmldata) columns

                  "SupplierId" number path 'SupplierDto/SupplierId',

                  "Id" number path 'Cities/CitiesDto/Id', "Price" number path 'Price'

                  ) xt;

                   

                  The Price node you are trying to retrieve is empty.

                   

                  <ArrayOrdersDto xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.xyz">

                    <OrdersDto>  

                      <Price xmlns:d3p1="http://schemas.xyz" />

                      ...

                   

                  You also have a <Price> in <SupplierDto>.

                   

                        <SupplierDto>

                          <SupplierId>0</SupplierId>

                          <Price>32400</Price>      

                        </SupplierDto>

                        <SupplierDto>

                          <SupplierId>1</SupplierId>

                          <Price>32400</Price>

                        </SupplierDto>

                   

                  Which <Price> are you really after?

                   

                  You have two Cities elements. One is a child of the other.

                   

                      <Cities xmlns:d3p1="http://schemas.xyz">

                        <d3p1:Cities>

                          <d3p1:CitiesDto>        

                            <d3p1:Id>21710035</d3p1:Id>        

                          </d3p1:CitiesDto>

                          <d3p1:CitiesDto>  <!-- I added this element in here -->      

                            <d3p1:Id>21710022</d3p1:Id>        

                          </d3p1:CitiesDto>

                         </d3p1:Cities>

                        <d3p1:Id>21464853</d3p1:Id>

                        <d3p1:LastModifiedDate>0001-01-01T00:00:00</d3p1:LastModifiedDate>

                      </Cities>

                   

                  You have multiple Suppliers. It seems although only one is given in the sample xml, there is a possibility of more than one <CitiesDto> to be present. Is it a correct assumption?

                   

                  For each SupplierId, do you want to repeat the CitiesDto/Id in the case of one. What if you had 2 CitiesDto/Id and 2 SupplierId elements for instance?

                  • 6. Re: Error parsing xml - Xquery dynamic type mismatch
                    883125

                    Yes there can be more than one cities dto present. In case of more than one cities dto/Id for a supplier element I would only like to retrieve the first city id.

                    • 7. Re: Error parsing xml - Xquery dynamic type mismatch
                      mNem

                      with x (xmldata) as
                      (
                      select '
                      <ArrayOrdersDto xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.xyz">
                        <OrdersDto>  
                          <Price xmlns:d3p1="http://schemas.xyz" />
                          <Suppliers>
                            <SupplierDto>
                              <SupplierId>0</SupplierId>
                              <Price>32400</Price>      
                            </SupplierDto>
                            <SupplierDto>
                              <SupplierId>1</SupplierId>
                              <Price>32400</Price>
                            </SupplierDto>
                          </Suppliers>
                          <Cities xmlns:d3p1="http://schemas.xyz">
                            <d3p1:Cities>
                              <d3p1:CitiesDto>        
                                <d3p1:Id>21710035</d3p1:Id>        
                              </d3p1:CitiesDto>
                            </d3p1:Cities>
                            <d3p1:Id>21464853</d3p1:Id>
                            <d3p1:LastModifiedDate>0001-01-01T00:00:00</d3p1:LastModifiedDate>
                          </Cities>
                          <Id>32458</Id>
                         </OrdersDto>
                      </ArrayOrdersDto>
                      ' from dual

                      )
                      select xt.*
                      from
                      x,
                      xmltable (
                         xmlnamespaces (
                                   'http://schemas.xyz' as "d3p1"
                                  , default 'http://schemas.xyz'
                         )
                         ,
                         '/ArrayOrdersDto/OrdersDto/Suppliers/SupplierDto'
                         passing xmltype(x.xmldata)
                         columns
                                "SupplierId" number path 'SupplierId'
                                , 
                                "Price" number      path 'Price'
                                ,
                                "Id" number         path './../../Cities/d3p1:Cities/d3p1:CitiesDto[1]/d3p1:Id'
                      ) xt;

                       

                      SupplierId      Price         Id
                      ---------- ---------- ----------
                               0      32400   21710035
                               1      32400   21710035