EMAIL

Querona reads email message files from a File connection. Every message (.msg, .eml or .ems) in the connection’s folders becomes a row in the built-in builtin.EmailMessages table, exposing the headers, sender and recipients, the body in several formats, and the attachments. You can also read messages ad-hoc — without importing metadata — with OPENROWSET (BULK …) and FORMAT = 'EMAIL'.

The EmailMessages table

Column name	Column type	Description
MessageId	VarChar(160) not null	Message id
Subject	NVarChar(250) null	Message subject
SubjectNormalized	NVarChar(250) null	Subject with prefixes (Re:, Fwd:) removed
ReceivedOn	DateTime2 null	Received date
BodyHtml	NVarChar(max) null	Body in HTML
BodyRtf	NVarChar(max) null	Body in RTF
BodyText	NVarChar(max) null	Body in plain text
Attachments	Int not null	Attachment count
AttachmentsJson	NVarChar(max) null	Attachment metadata as a JSON array
Sender_DisplayName	NVarChar(200) null	Sender display name
Sender_Email	NVarChar(200) null	Sender email address
Recipients_To	NVarChar(max) null	To recipients
Recipients_CC	NVarChar(max) null	Cc recipients
SentOn	DateTime2 null	Sent date
CreatedOn	DateTime2 null	Created date
Categories	NVarChar(220) null	Categories
ConversationIndex	NVarChar(160) null	Conversation index
ConversationTopic	NVarChar(200) null	Conversation topic
LastModifiedOn	DateTime2 null	Last modification date
Headers	VarChar(2000) null	Raw message headers
Importance	VarChar null	Importance
InternetCodePage	VarChar null	Internet code page
MessageLocalId	VarChar(160) null	Message local id
Type	VarChar(30) null	File extension
Path	NVarChar(4000) null	OS file path

The table also has Attachment0 … Attachment50 (varbinary(max)) holding the raw bytes of up to 51 attachments; AttachmentsJson describes them (name, size, content type) and Attachments is the count.

Reading email

-- built-in table
SELECT Subject, Sender_Email, ReceivedOn, BodyText FROM builtin.EmailMessages;

-- ad-hoc, one or more files
SELECT Path, Subject, Attachments
  FROM OPENROWSET(BULK '*.msg', DATA_SOURCE = 'files', FORMAT = 'EMAIL') AS mail;

-- list attachments per message (AttachmentsJson is a JSON array)
SELECT m.Subject, a.[value] AS attachment
  FROM OPENROWSET(BULK '*.msg', DATA_SOURCE = 'files', FORMAT = 'EMAIL') AS m
       CROSS APPLY OPENJSON(m.AttachmentsJson) AS a;

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

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