SQL Server JDBC - Getting FOR XML into Java

Author

Getting FOR XML into Java

spjo55@gmail.com

2006-02-25, 9:28 am

Hello there

I have a question regarding the XML support in SQL Server 2005 and the
new Microsoft JDBC driver. I hope to get XML from SQL Server to do
XSLT-processing at the web-tier, but lack some sample code of how get
XML through JDBC when I do a SELECT .... FOR XML.

In this article
http://www.windowsitpro.com/Article...2324/22324.html Rich
Rollman describes a "character-encoding mismatch between SQL Server
and Java" and suggests this code:

// Display the SQL results.
while(rs.next())
{
InputStream is = rs.getBinaryStream(1);
InputStreamReader isr = new
InputStreamReader(is
,"UnicodeLittle");

while ((c = isr.read()) != -1)
{
bw.write(c);
sb.append((char) c);
}
}

The article/code above is from 2001, and my question is how best to
tackle this with SQL Server 2005 and the new driver. I can do it by
building a String in the while loop, but I think that there might be a
better way.

Eventually I will need to pass the XML as a javax.xml.transform.Source
to do the transformation.

Thanks

Jorgen

David Browne

2006-02-25, 9:29 am

<spjo55@gmail.com> wrote in message
news:1140830038.866049.255580@z34g2000cwc.googlegroups.com...
> Hello there
>
> I have a question regarding the XML support in SQL Server 2005 and the
> new Microsoft JDBC driver. I hope to get XML from SQL Server to do
> XSLT-processing at the web-tier, but lack some sample code of how get
> XML through JDBC when I do a SELECT .... FOR XML.
>
> In this article
> http://www.windowsitpro.com/Article...2324/22324.html Rich
> Rollman describes a "character-encoding mismatch between SQL Server
> and Java" and suggests this code:
>
> // Display the SQL results.
> while(rs.next())
> {
> InputStream is = rs.getBinaryStream(1);
> InputStreamReader isr = new
> InputStreamReader(is
,"UnicodeLittle");
>
> while ((c = isr.read()) != -1)
> {
> bw.write(c);
> sb.append((char) c);
> }
> }
>
> The article/code above is from 2001, and my question is how best to
> tackle this with SQL Server 2005 and the new driver. I can do it by
> building a String in the while loop, but I think that there might be a
> better way.
>
> Eventually I will need to pass the XML as a javax.xml.transform.Source
> to do the transformation.
>

I'm not sure what the best way is, but with SQL 2005 you can easilly get the
XML as a string. EG

declare @xml xml
select (select * from sys.objects for xml auto, type)
select cast(@xml as varchar(max)) xml

David

spjo55@gmail.com

2006-02-28, 8:24 pm

Thanks David,

That works.

Still I think it would be good if MS provided more detailed
samples/scenarios on XML in their 'Using Advanced Data Types'-section
of the documentation.

Jorgen

Acey Bunch [MSFT]

2006-03-14, 8:23 pm

Thanks for the feedback Jorgen.

We do currently have plans to ship a number of more robust sample
applications with future releases of the driver, and working with XML is
certainly going to one of them.

Again, thanks for the feedback.

Acey Bunch
Lead Programmer Writer
Microsoft JDBC Driver Team

<spjo55@gmail.com> wrote in message
news:1141124041.175210.170160@z34g2000cwc.googlegroups.com...
> Thanks David,
>
> That works.
>
> Still I think it would be good if MS provided more detailed
> samples/scenarios on XML in their 'Using Advanced Data Types'-section
> of the documentation.
>
> Jorgen
>

Sponsored Links