Topic "Help with SQL 2000 OPENXML and WinSCP xml log file"

Author Message
[View user's profile]

Joined: 2011-09-08
Posts: 1
Hi all, I'm going crazy trying to figure out how to use OPENXML in a SQL 2000 select statement to query a WinSCP xml log file. All I want to do is query the file names and modification dates out of the log. I'm stuck using SQL 2000 for this, so I can't use the tools in '05 or '08. I thought someone here would have done this before and could help me out. Thanks all in advance!

Here's the sql I'm using, but I'm not getting any results. Ideally, I'd like a column with the file name and another with the modification dates.

exec sp_xml_preparedocument @i output, @xml

select *
from openxml(@i, '/session/ls/files/file/filename')
with (value varchar(1000))

exec sp_xml_removedocument @i

Here's the xml that's in the variable-

<?xml version="1.0" encoding="UTF-8"?>
<session xmlns="" name="Test" start="2011-09-07T22:24:39.467Z">
<destination value="/CAWARR" />
<filename value=".." />
<type value="D" />
<modification value="1899-12-30T07:00:00.000Z" />
<permissions value="---------" />
<filename value="CAWARR.175919" />
<type value="-" />
<size value="6550" />
<modification value="2011-09-06T23:59:00.000Z" />
<permissions value="---------" />
<filename value="CAWARR.173346" />
<type value="-" />
<size value="3144" />
<modification value="2011-09-07T23:33:00.000Z" />
<permissions value="---------" />
<result success="true" />
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 26553
Location: Prague, Czechia
I do not know OPENXML, but I would suggest that you need to specify, what namespace ( the XML tag names in your query, are from.
Martin Prikryl

You can post new topics in this forum


What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!


About donations

$9   $19   $49   $99

About donations


WinSCP Privacy Policy

WinSCP License