on 2017 Apr 21 8:29 AM
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.
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".
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
71 | |
11 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.