I do not know OPENXML, but I would suggest that you need to specify, what namespace (https://winscp.net/schema/session/1.0) the XML tag names in your query, are from.

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="https://winscp.net/schema/session/1.0" name="Test" start="2011-09-07T22:24:39.467Z">
<ls>
<destination value="/CAWARR" />
<files>
<file>
<filename value=".." />
<type value="D" />
<modification value="1899-12-30T07:00:00.000Z" />
<permissions value="---------" />
</file>
<file>
<filename value="CAWARR.175919" />
<type value="-" />
<size value="6550" />
<modification value="2011-09-06T23:59:00.000Z" />
<permissions value="---------" />
</file>
<file>
<filename value="CAWARR.173346" />
<type value="-" />
<size value="3144" />
<modification value="2011-09-07T23:33:00.000Z" />
<permissions value="---------" />
</file>
</files>
<result success="true" />
</ls>
</session>