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