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?

asked 21 Apr, 07:29

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334481
accept rate: 12%

edited 21 Apr, 08:09

Volker%20Barth's gravatar image

Volker Barth
30.0k295449654

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

(21 Apr, 07:56) Breck Carter

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

(25 Apr, 03:17) Volker Barth

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

permanent link

answered 21 Apr, 07:57

Volker%20Barth's gravatar image

Volker Barth
30.0k295449654
accept rate: 32%

edited 21 Apr, 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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×197
×12

question asked: 21 Apr, 07:29

question was seen: 65 times

last updated: 25 Apr, 03:18