XML

Querona reads XML files from a File connection. Every XML file (.xml by default) in the connection’s folders becomes a row in the built-in builtin.XmlFiles table, with the file’s content in the Xml column. You can also read XML files ad-hoc — without importing metadata — with OPENROWSET (BULK …) and FORMAT = 'XML'.

Note

This article is about reading XML files. To work with the xml data type in queries — FOR XML, OPENXML, and the xml methods — see XML.

The XmlFiles table

Column name	Column type	Description
Name	NVarChar(255) null	File name with extension
SizeBytes	BigInt not null	File size in bytes
Path	NVarChar(4000) not null	OS file path
Encoding	VarChar(50) null	Character encoding
Type	NVarChar(30) null	File extension
Xml	NVarChar(max) null	Full file content

The Xml column is nvarchar(max) (the file text), not the xml data type. CAST it to xml to shred it with OPENXML or the xml methods.

Reading XML files

-- built-in table
SELECT Name, Xml FROM builtin.XmlFiles;

-- ad-hoc, one or more files
SELECT Path, Xml
  FROM OPENROWSET(BULK '*.xml', DATA_SOURCE = 'files', FORMAT = 'XML') AS docs;

-- shred a value out of each file
SELECT Path, CAST(Xml AS xml).value('(/order/id)[1]', 'int') AS order_id
  FROM OPENROWSET(BULK '*.xml', DATA_SOURCE = 'files', FORMAT = 'XML') AS docs;

DATA_SOURCE names the File connection; a file reference is resolved under its directory, and a reference that escapes it is rejected. See Files and data services for connection setup and folder-to-schema mapping.

See also

• Files and data services — the File provider: connections, folder mapping, and the other file types

• XML — the xml data type (FOR XML / OPENXML / xml methods)

• OPENROWSET — the OPENROWSET (BULK ...) reference