Post a reply

Options
Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)

Options

Topic review

martin

Re: Help with SQL 2000 OPENXML and WinSCP xml log file

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.
EricT

Help with SQL 2000 OPENXML and WinSCP xml log file

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>