Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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

, 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:

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?

asked 21 Apr '17, 07:29

Reimer%20Pods's gravatar image

Reimer Pods
accept rate: 11%

edited 21 Apr '17, 08:09

Volker%20Barth's gravatar image

Volker Barth

Here's an untested WAG...

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" :)

(21 Apr '17, 07:56) Breck Carter

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

(25 Apr '17, 03:17) Volker Barth

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

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 *
   openxml(xp_read_file(xmlPath), '//*:name')
   with (element varchar(255) '@mp:localname');

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

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

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

permanent link

answered 21 Apr '17, 07:57

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 21 Apr '17, 08:08

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 21 Apr '17, 07:29

question was seen: 2,187 times

last updated: 25 Apr '17, 03:18