AppletTalk.com

Sandy · Guest

Hi,

I want to use the MS-Sqlserver's OPENXML function in my java application (to
load data from xml file into database)
I am using mssqlserver.jar (JDBC fro sqlserver by MS).
Can somebody tell me how do i execute the OPENXML function from JDBC.

Thanks

Dai Wei · Guest

Copy from:
http://www.jnetdirect.com/jsqlconnectTechDocs/Documentation/TechnicalReference.html

The following example shows how to build a JDBC result set from an XML
document using SQL Server's sp_xml_preparedocument procedure.

/* Parse the XML document and select the required columns */

String exec =
"DECLARE @_hDoc int "+
"EXEC sp_xml_preparedocument @_hDoc OUTPUT, "+
"N'<ROOT> "+
" <Customers CustomerID="Customer1" ContactName="Joe"
CompanyName="Company1"> "+
" <Orders CustomerID=
"Customer1"OrderDate="2000-08-25T00:00:00"/>" +
" <Orders CustomerID="Customer1"
OrderDate="2000-10-03T00:00:00"/> "+
" </Customers> "+
" <Customers CustomerID="Customer2" ContactName="David"
CompanyName="Company2"> "+
" <Orders CustomerID=
"Customer2"OrderDate="2000-09-23T00:00:00"/>" +
" </Customers> "+
"</ROOT>'" +
"SELECT * FROM OPENXML(@_hDoc, N'/ROOT/Customers/Orders') with
(CustomerID nchar(10) '../@_CustomerID', OrderDate datetime) " +
"EXEC sp_xml_removedocument @_hdoc";

/* Retrieve and display the result set */

s = s.executeQuery(exec);
ResultSetMetaData md = rs.getMetaData();
while (rs.next()) {
for (int i=0; i System.out.print(rs.getString(i+1)+" ");
System.out.println();
}