Help with SQL 2000 OPENXML and WinSCP xml log file

Advertisement

EricT
Joined:
Posts:
1

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>

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

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.
_________________
Martin Prikryl

Reply with quote

Advertisement

You can post new topics in this forum