How to write OPENXML query with USING FILE

[reimer_pods]

During my first steps using OPENXML I've tried to figure out, how this version of the syntax (Syntax 2) could be used.

OPENXML( { USING FILE | USING VALUE }
xml-data
, xpath
[, flags
[, namespaces ] ]
)
WITH ( column-name column-type
[ xpath ] [ , ... ]
) 
[ OPTION ( scan-option ) ]
[ AS ] correlation-name

I've got this working example, where the file is read via xp_read_file:

SELECT * 
FROM openxml( xp_read_file( 'H:\\Zeichnung.xml' ), '/Zeichnung' )
WITH ( CustNo CHAR(30) 'Kunde',
       ArtID   INTEGER  'Artikelnummer',
       PFilepath VARCHAR(254) 'Datei');

I'd like to modify the query to the above syntax, but haven't got a clue how to write it. Browsing for any examples using this syntax didn't give useful hints.
Does anyone know how to rewrite the query accordingly?

[Breck_Carter]

Here's an untested WAG...

SELECT * 
FROM openxml( USING FILE 'H:\\Zeichnung.xml', '/Zeichnung' )
WITH ( CustNo CHAR(30) 'Kunde',
       ArtID   INTEGER  'Artikelnummer',
       PFilepath VARCHAR(254) 'Datei')
AS correlation-name;

I'm guessing it's a special syntax for using in big FROM clauses, with files or columns providing the xml.

"Having invented another wonderful feature, the SQL Anywhere engineer moves on, never to touch the Help topic again" 🙂

[VolkerBarth]

FWIW, Jack has added according samples to the v17 DCX page, see the comments section:)

[VolkerBarth · Accepted solution]

Syntax 2 requires a correlation name whereas syntax 1 does not, so the following should do:

SELECT * 
FROM openxml( using file 'H:\\Zeichnung.xml', '/Zeichnung' )
WITH ( CustNo CHAR(30) 'Kunde',
       ArtID   INTEGER  'Artikelnummer',
       PFilepath VARCHAR(254) 'Datei') DT;

FWIW, some tests with a built-in XML file from the v16 MobiLink/Setup/Dnet subdirectory:

-- 2 variables to store file path resp. XML data
create or replace variable xmlPath varchar(255);
create or replace variable xmlValue xml;

set xmlPath =
  cast(xp_getenv('SQLANY16') as varchar(255)) || '\\\\MobiLink\\\\Setup\\\\Dnet\\\\mlDomConfig.xml';
set xmlValue = xp_read_file(xmlPath);
select xmlPath, xmlValue;

-- Syntax 1:
select *
from
   openxml(xp_read_file(xmlPath), '//*:name')
   with (element varchar(255) '@mp:localname');

select *
from
   openxml(xmlValue, '//*:name')
   with (element varchar(255) '@mp:localname');

-- Syntax 2 - note the "DT" correlation name:
select *
from
   openxml(using value xmlValue, '//*:name')
   with (element varchar(255) '@mp:localname') DT;

select *
from
   openxml(using file xmlPath, '//*:name')
   with (element varchar(255) '@mp:localname') dt;

Each query should return a result set with one colum "element" and two rows "name".