Subject:xpath query in SQL server Author:Ray Waldron Date:04 Apr 2007 07:59 AM
Hi all,
Can someone please help.
I am trying to export a table in SQL 2000 to XML.
From My readings. I need to create an XML Schema that will be used to define the finished XML from the Table.
I have used Stylus Studio to create an XML from the table and then an XML Schema from the XML..
My question is...
I now have a table in SQL 2000 and an XML schema to create an XML but how do I run this in SQL 2000 and then schedule it to run every day.
I think I need to Use xpath query that creates the xml but I don't know where to start this. Can you please help.
Or is this all to be done using Stylus Studio and linked to SQL DB
Ray..
Subject:xpath query in SQL server Author:Ray Waldron Date:04 Apr 2007 11:43 AM
Hiya Thx for the reply..
I have viewed the tutorial..Very good..
However I think this may be overkill..
Jus a couple of quick questions.
How do I quickly use the xquery to create an xml from all columns in the linked SQL table?
How do I schedule this to run evry say 1 Hour?
and where does the xml get saved ?
Subject:xpath query in SQL server Author:(Deleted User) Date:05 Apr 2007 04:04 AM
Hi Ray,
in order to simply get the data, just drag the table into the XQuery editor, and pick the collection('xxx') choice; running this query will get you a dump of the data in the default format.
If you need to run this with a schedule, you can enter the target file name in the Output URL setting of the scenario, choose the DDXQ processor and then generate a Java class to run the XQuery. At that point you can compile it and set up the task scheduler/cron to run it with the required frequency.
Be advised that this could require additional runtime licenses for either DDXQ and/or Stylus runtime components; if you don't need the performance and scalability provided by DDXQ or the flexibility of having a Java code that can be deployed on different platform (including web services/application servers) you can try using the free command line tool StylusXql to run the original XQuery.