3 Replies Latest reply on Feb 28, 2011 10:05 AM by kochk

    SQL query against  web service vdb

    kochk

      I am running a select query against a VDB for a web service (ie   select xmlserialize(document w.result as string) .......)

      The query is returning the result below.

       

        <ns1:processResponse xmlns:ns1="http://com.test.ws/exampleWebService">

          <return>Hello</return>

         </ns1:processResponse>

       

      I am now trying to change the select statement to return just the response value of "Hello".  I tried xmlpathvalue('ns1:processResponse/return') and get an error,  xmlpathvalue('processResponse/return') and get null.  I played with variations of xmlquery trying to declare the namespace, but just cannot get the right syntax. 

       

      Can somebody help me out. 

        • 1. SQL query against  web service vdb
          shawkins

          Hi Kellie,

           

          The simplest extraction ignoring namespaces would be:

           

          xpathValue(val, '/*:processResponse/*:return')

           

          That uses the Saxon wildcard matching for namespaces.  The equivalent in more standard xpath is:  

           

          xpathValue(val, '/*[local-name()="processResponse"]/*[local-name()="return"]')

           

          Both of those will work fine in Teiid.  If you are extracting more than just the string value or want to be namespace aware then xmlquery is the way to go.

           

          Steve

          • 2. SQL query against  web service vdb
            shawkins

            An example of namespaces with XMLQUERY to extract just the hello value from this doc would be:

             

            XMLSERIALIZE(XMLQUERY(XMLNAMESPACES(NO DEFAULT, 'http://com.test.ws/exampleWebService' AS ns1), '/ns1:processResponse/return/text()' PASSING value NULL ON EMPTY) AS STRING)

             

            where value is your xml document value.

             

            Steve

            • 3. SQL query against  web service vdb
              kochk

              Thanks Steven!  Both of those examples worked for me.