How to save XML file as .xml which is output from SQL Server in Java?

Question

I want to run SQL query in Java and the result of the query is in xml. The query result is as follows -

<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Rows>
  <Unique_id>6124</Unique_id>
  <DoorNo>12</DoorNo>
  <StreetNo>1</StreetNo>
  <SiteNo>84904</SiteNo>
</Rows>
<Rows>
  <Unique_id>6125</Unique_id>
  <DoorNo>12</DoorNo>
  <StreetNo>2</StreetNo>
  <SiteNo>84904</SiteNo>
</Rows>
</Root>

Now I want to export the above xml into an external file called QueryResult.xml using java. I have tried using the below java script but unable to save the file.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");   
con=DriverManager.getConnection("jdbc:sqlserver://sqldatabase:1009;
databaseName=DatabaseName;user=UserName;password=1234");
PreparedStatement ps=con.prepareStatement(QueryName);  
ResultSet rs=ps.executeQuery();
SQLXML xmlVal1= rs.getSQLXML(1);
String val = xmlVal1.getString();
try (PrintWriter out = new PrintWriter(new File("Output.xml"))) {
    out.println(val);
}

Query Name:

  select Distinct Unique_id, DoorNo, StreetNo, Siteno from Table Name where 
  Unqiue_id IN ( '6124','6125') FOR XML RAW ('Rows'), ROOT ('Root'), ELEMENTS XSINIL;

Alexander · Accepted Answer · 2018-06-27 09:46:38Z

1

SQLXML xmlVal= rs.getSQLXML(1);
String val = xmlVal.getString();
try (PrintWriter out = new PrintWriter("out.xml")) {
    out.println(val);
}

Here is the Oracle Tutorial with code examples.

Regarding your code, 1. you forgot to make next() on result set; 2. you don't need SQLXML here at all.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");   
con=DriverManager.getConnection("jdbc:sqlserver://sqldatabase:1009;
databaseName=DatabaseName;user=UserName;password=1234");
PreparedStatement ps=con.prepareStatement(QueryName);  
ResultSet rs=ps.executeQuery();
rs.next();
String val = rs.getString(1);
try (PrintWriter out = new PrintWriter(new File("Output.xml"))) {
    out.println(val);
}

edited Jun 27, 2018 at 9:46

answered Jun 27, 2018 at 8:04

Alexander

614 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to save XML file as .xml which is output from SQL Server in Java?

1 Answer 1

Here is the Oracle Tutorial with code examples.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Here is the Oracle Tutorial with code examples.

Comments

Your Answer

Sign up or log in

Post as a guest

Related