Microsoft only supports XQuery via SQL Server. So what if you wanted to run a XQuery 1.0 command and save the resuilts to a disk file?
Saving to disk files can be a little tricky from SQL. So instead, we can just use SQL as our XQUery engine, run the query, and then save the results to disk.
This was a concept I came up with in my new XML course. Normally, I would just use the Saxonia .NET Transform.exe command (and it supports XQuery 3.0 and 3.1).
But if for some odd reason, we wanted to go “all Microsoft”, then the program below would do the job.
This may also just be interested to SQL developers, who didn’t know such a thing is even possible.
We use the “OPENROWSET” command to read an XML input file from the disk.
Xquery can also be run against XML Columns in an SQL table. But for purposes of learning, a person might want to run some of the simple XQuery examples that he or she finds on various websites. And of course, they all take XML files as input. You can download or copy/paste the XQuery and the data file(s), and then process them with the PowerShell below.
This of course assumes you have SQL server installed, and you have enough privileges to run the OPENROWSET command.
The entire SQL command is passed in a PowerShell “here doc” or “here string”. The timeout was increased to 180 seconds, because the default was not enough.
<pre>
cls
$datasource = "server=server\instance;database=mydb;trusted_connection=true"
$datasource = "server=(local);database=master;trusted_connection=true"
#if not using Integrated Security,
#you might want to pass user/pass in variables or prompt for them
#$connectionString = 'User Id=' + $username + ';Password=' + $password + ';' + $datasource
$connectionString = $datasource
write-host $connectionString
$connection = New-Object System.Data.SQLClient.SQLConnection($connectionString)
$connection.open()
Write-Host "SQL Connection Opened"
$SQLScalarCommand = @'
SELECT Cast((SELECT BulkColumn FROM OPENROWSET(BULK'c:\XMLClass\Shakespeare\Hamlet\hamlet.xml',SINGLE_CLOB) as myalias) as XML).query(
'
<html><body>
{
for $act in //ACT
return $act
}
</body></html>
')
'@
#Write-Host $SQLScalarCommand
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $connection
$Command.CommandText = $SQLScalarCommand
$SQLResult = $Command.ExecuteScalar().ToString()
Write-Host ("Result=$SQLResult")
$connection.close() #not sure if this helps...
#but in case you have more code after this...
<pre>You can also run the query above directly in SSMS (SQL Server Management Studio), and view the results there.
XQuery typically returns XML or HTML, but can also return any text.