Archive for the 'SQL Server' Category

SQL – XML in SQL Server 2000

September 13, 2006

I managed to try out the sp_makewebtask T-SQL stored procedure for exporting the result of an SQL query to XML file that I mentioned in an earlier post. I’m happy to say that it works a treat! I’m quite excited about it and would love to know if there is something similar for mysql now that stored procedures are supported (mysql 5).

The conversion is as simple as creating a .tpl file with the following (root can either be removed or changed to represent a doc type definition, much more useful then just root!):

<root>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</root>

And then calling the stored procedurce in the query analyser, or however way you wish to call it.

sp_makewebtask @outputfile = ‘c:\temp\myxmlfile.xml’,
@query = ‘select * from sysobjects for xml auto’,
@templatefile = ‘c:\temp\template.tpl’

The result is an XML file created in c:\temp called myxmlfile.xml. I tried a few different queries such as: select * from customer where custID=1… which created a node called customer with the customer details as attributes.

<root>

<Customers custID=”1″ custFirstName=”John” custLastName=”Smith” custAddress=”123 Queen St”/>

</root>

I see this as being useful for sites such as del.icio.us where a user may want to export their url list to a file (del.icio.us actually offer this service, just using it as an example).

export XML from SQL

August 12, 2006

This is the next thing I need to do for an application I am working on, won’t get to try it until next week but it looks like exactly what I am after, and quite simple! Fingers crossed.

http://www.sqlxml.org/faqs.aspx?faq=29

MozillaActiveX

The Mozilla Active X control worked perfectly as can be seen in the screenshot above, the only problem is that it doesn’t seem to support xml? I opened my wordpress feed in the control and it outputted plain text, not the nested elements as it would if you opened it in firefox for example. This is what I wanted to use it for (xml with a stylesheet) so I think I’ll look for a control that supports this, most likely a Java/Swing combo. Python/ Ruby were close but I have more experience in Java and like using Java Swing GUI components. And having to worry about how the TK or QT frameworks work with each language is proberly not the best way to learn a new language :).

I have been having a play with Ruby and I realling love the language and the pure OO implementation. It is very similar to JADE however I think it has already got more market interest! Especially with the Ruby on Rails framework.