Hi,

I can create a a block of code like this and it will work:

DECLARE @id varchar(50)
SET @id = 'bla'
SELECT RECID FROM MYTABLE WHERE "MYCOL".exist('//c3[text()=(sql:variable("@id"))]' ) = 1

But how do I get the same with a jdbc prepared statement without using the above DECLARE/SET?  I've tried:

SELECT RECID FROM MYTABLE WHERE "MYCOL".exist('//c3[text()=(sql:variable(?))]' ) = 1

SELECT RECID FROM MYTABLE WHERE "MYCOL".exist('//c3[text()=?]' ) = 1

I want to bind the ? char above with 'bla'.  The issue seems to be that the ? char is in the middle of the XQuery and isn't getting bound correctly.  Is this DECLARE/SET mandatory in this situation or is there a clever way to do it like Oracle would, ie:

SELECT RECID FROM MYTABLE WHERE  XMLEXISTS('$t[c3/text()=$id]' PASSING "MYCOL" as "t", CAST(? AS VARCHAR(4000)) as "id")

where I can bind the ? char with PreparedStatement.setString(1,'bla') without any DECLARE/SET syntax.

Thanks for any help!

Couldn't you do

DECLARE @id varchar(50) = ?
SELECT RECID FROM MYTABLE WHERE "MYCOL".exist('//c3[text()=(sql:variable("@id"))]' ) = 1

Hi SQLSERVERTEM,

You can wrap those DECLARE and SET in the sql string as well. Just seperate each statement with ";". Please see the below sample.

import java.sql.*;

public class tEST{
public static void main(String[] srg) {
  String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";  
  String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=testDB1";  
  String userName = "sa";   
  String userPwd = "123456";   
  Connection dbConn;
  try {
   Class.forName(driverName);
   dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
   System.out.println("Connection Successful!");  
   
   String sql = "DECLARE @id varchar(50); SET @id = ? ; SELECT RECID FROM MYTABLE WHERE \"MYCOL\".exist('//text[text()=(sql:variable(\"@id\"))]' ) = 1";
   PreparedStatement ps = dbConn.prepareStatement(sql);
   ps.setString(1, "bla");
   ResultSet rs = ps.executeQuery();
   while(rs.next()){
   System.out.println(rs.getInt("recid")); 
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
}
}

Or you may choose to concatenate the sql string as below.

   String sql = "SELECT RECID FROM MYTABLE WHERE \"MYCOL\".exist('//text[text()="+"\"bla\""+"]' ) = 1";
   System.out.println(sql); 
   PreparedStatement ps = dbConn.prepareStatement(sql);
   //ps.setString(1, "bla");
   ResultSet rs = ps.executeQuery();
   while(rs.next()){
   System.out.println(rs.getInt("recid")); 
   }

If you have any question, feel free to let me know.

Hi,

Thanks for your reply.  Yes that works.  I don't see a way to just encapsulate it in a SELECT however, so it looks like that is the approach I'll have to go with.

Thanks!