Question Re: How to write OPENXML query with USING FILE in Technology Q&A

How to write OPENXML query with USING FILE

reimer_pods — Fri, 21 Apr 2017 07:29:42 GMT

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?

Re: How to write OPENXML query with USING FILE

Breck_Carter — Fri, 21 Apr 2017 07:56:54 GMT

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" 🙂

Re: How to write OPENXML query with USING FILE

VolkerBarth — Fri, 21 Apr 2017 07:57:17 GMT

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

Re: How to write OPENXML query with USING FILE

VolkerBarth — Tue, 25 Apr 2017 03:17:35 GMT

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