SQLXML不适用于java。

Question

环境详情如下：

Appserver是：- apache tomcat 8 jre 1.8 (最新日期为2015年3月1日)数据库为MS-SQL Server 2008

下面是代码片段：-

<%@ page import="java.util.*,java.io.*,org.xml.sax.* ,javax.xml.*, org.w3c.dom.*, javax.sql.*, javax.xml.transform.*, javax.xml.transform.sax.* "%>

<% 
java.sql.Connection con;
java.sql.Statement s;
java.sql.ResultSet rs;
java.sql.PreparedStatement pst;
con=null;s=null;pst=null;rs=null;
//String url= "jdbc:sqlserver://dB_IP/dBName"; 
String url= "jdbc:sqlserver://IPADDRESS;databaseName=dBName;integratedSecurity=false;user=userid;password=password;";
//String url= "jdbc:jtds:sqlserver://dB_IP/dBName";
String id= "user_id";
String pass = "password";
try{
//Class.forName("net.sourceforge.jtds.jdbc.Driver");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//con = java.sql.DriverManager.getConnection(url, id, pass);
con = java.sql.DriverManager.getConnection(url);
}catch(ClassNotFoundException cnfex){
cnfex.printStackTrace();
}
String sql = null,root1=null,root2=null,content_type="text/xml";
if(request.getParameter("sql")!=null){sql=request.getParameter("sql");}else{sql="select * from test_table for xml raw, xmldata";}
if(request.getParameter("root")!=null){root1=request.getParameter("root");root2="</"+root1+">";root1="<"+root1+">";}else{root1="<parse>";root2="</parse>";}
if(request.getParameter("contenttype")!=null){content_type=request.getParameter("contenttype");}else{content_type="text/xml";}
try{
    out.println( "inside try" );
    s = con.createStatement();
    //SQLXML xmlVal = con.createSQLXML();
    String query = "select top 100 * from table for xml raw, xmldata";
    rs = s.executeQuery(query);
        while (rs.next()) {
            out.println( "<br/>inside while " + query);
            java.sql.SQLXML sqlxml = rs.getSQLXML(1);
            //InputStream binaryStream = sqlxml.getBinaryStream();
/*
            java.sql.SQLXML rssFeedXML = rs.getSQLXML(1);
            javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory.newInstance();
            factory.setNamespaceAware(true);
            javax.xml.parsers.DocumentBuilder builder = factory.newDocumentBuilder();
            Document doc = builder.parse(rssFeedXML.getBinaryStream());
            //System.out.println("RSS identifier: " + rssName);
            //out.println(JDBCTutorialUtilities.convertDocumentToString(doc));
            out.println( (doc) );
*/
/*
//http://docs.oracle.com/javase/6/docs/api/java/sql/SQLXML.html

            Transformer identity = TransformerFactory.newInstance().newTransformer();
            Source source = sqlxml.getSource(null);
            Result result = new StreamResult(System.out);
            identity.transform(source, result);
*/
        }
        out.flush();out = pageContext.pushBody();
        out.println( "Hi " );
}
catch(Exception e){e.printStackTrace();out.println( "<br/>Caught " + e);}
finally{
if(rs!=null) rs.close();
if(s!=null) s.close();
if(con!=null) con.close();
}

%>

编译错误：-

捕捉到com.microsoft.sqlserver.jdbc.SQLServerException：，不支持从ntext到SQLXML的转换。

并且要求“：-从select语句返回一个xml文档。xml应用程序是必需的。

，如果有什么建议而不是这种方法，你可以告诉我.

Answer 1

我今天遇到了同样的问题。似乎元数据没有正确地使用"FOR“指令填充。

为了克服这个问题，我首先使用变量并将xml保存到变量中，如下所示：

DECLARE @tmpXML XML 
SET @tmpXML = (
SELECT * FROM (SELECT 1 as one) T FOR XML AUTO, ELEMENTS)
SELECT @tmpXML as [I can even have a name]

然后，可以使用getSQLXML方法从JDBC中获取XML。