SQL on FHIR, published by HL7. This guide is not an authorized publication; it is the continuous build for version 0.0.1-pre built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/FHIR/sql-on-fhir-v2/ and changes regularly. See the Directory of published versions

• Content
• Detailed Descriptions
• Mappings
• XML
• JSON

Logical Model: Tabular View Definition

A profile for View Definitions where each resulting field must contain only a simple scalar value. This is sometimes referred to as ‘CSV Mode’, but applies to any system that explicitly constrains its views or tables to tabluar data.

Usage:

• This Logical Model is not used by any profiles in this Implementation Guide

Formal Views of Profile Content

Description of Profiles, Differentials, Snapshots and how the different presentations work.

• Differential Table
• Key Elements Table
• Snapshot Table
• Statistics/References
• All

This structure is derived from ViewDefinition

Name	Flags	Card.	Type	Description & Constraints
ViewDefinition		0..*	ViewDefinition	Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where View Definition Instances of this logical model are not marked to be the target of a Reference
select
column	C	0..*	BackboneElement	A column to be produced in the resulting table. no-collections: Tabular view columns must not be collections. primitives-only: Tabular view columns only contain primitive values.
Documentation for this format

Constraints

Id	Grade	Path(s)	Details	Requirements
no-collections	error	ViewDefinition.select.column	Tabular view columns must not be collections. : collection.empty() or collection = false
primitives-only	error	ViewDefinition.select.column	Tabular view columns only contain primitive values. : type in ('base64Binary' | 'boolean' | 'canonical' | 'code' | 'dateTime' | 'decimal' | 'id' | 'instant' | 'integer' | 'integer64' | 'markdown' | 'oid' | 'string' | 'positiveInt' | 'time' | 'unsignedInt' | 'url' | 'uuid')

Name	Flags	Card.	Type	Description & Constraints
ViewDefinition		0..*	ViewDefinition	Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where View Definition Instances of this logical model are not marked to be the target of a Reference
status		1..1	code	draft | active | retired | unknown Binding: PublicationStatus (required)
resource		1..1	code	FHIR resource for the ViewDefinition Binding: ResourceType (required)
select	C	1..*	BackboneElement	A collection of columns and nested selects to include in the view. sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
column	C	0..*	BackboneElement	A column to be produced in the resulting table. no-collections: Tabular view columns must not be collections. primitives-only: Tabular view columns only contain primitive values.
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
path		1..1	string	FHIRPath expression that creates a column and defines its content
name	C	1..1	string	Column name produced in the output sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
Documentation for this format

Terminology Bindings

Path	Conformance	ValueSet	URI
ViewDefinition.status	required	PublicationStatus http://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard
ViewDefinition.resource	required	ResourceType http://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard

Constraints

Id	Grade	Path(s)	Details	Requirements
ele-1	error	**ALL** elements	All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count())
ext-1	error	**ALL** extensions	Must have either extensions or value[x], not both : extension.exists() != value.exists()
no-collections	error	ViewDefinition.select.column	Tabular view columns must not be collections. : collection.empty() or collection = false
primitives-only	error	ViewDefinition.select.column	Tabular view columns only contain primitive values. : type in ('base64Binary' | 'boolean' | 'canonical' | 'code' | 'dateTime' | 'decimal' | 'id' | 'instant' | 'integer' | 'integer64' | 'markdown' | 'oid' | 'string' | 'positiveInt' | 'time' | 'unsignedInt' | 'url' | 'uuid')
sql-expressions	error	ViewDefinition.select	Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1
sql-name	error	ViewDefinition.select.column.name	Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')

Name	Flags	Card.	Type	Description & Constraints
ViewDefinition		0..*	ViewDefinition	Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where View Definition Instances of this logical model are not marked to be the target of a Reference
url		0..1	uri	Canonical identifier for this view definition, represented as a URI (globally unique)
identifier		0..1	Identifier	Additional identifier for the view definition
name	C	0..1	string	Name of view definition (computer and database friendly) sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
title		0..1	string	Name for this view definition (human friendly)
meta		0..1	Meta	Metadata about the view definition
status		1..1	code	draft | active | retired | unknown Binding: PublicationStatus (required)
experimental		0..1	boolean	For testing purposes, not real usage
publisher		0..1	string	Name of the publisher/steward (organization or individual)
contact		0..*	ContactDetail	Contact details for the publisher
description		0..1	markdown	Natural language description of the view definition
useContext		0..*	UsageContext	The context that the content is intended to support
copyright		0..1	markdown	Use and/or publishing restrictions
resource		1..1	code	FHIR resource for the ViewDefinition Binding: ResourceType (required)
fhirVersion		0..*	code	FHIR version(s) of the resource for the ViewDefinition Binding: FHIRVersion (required)
constant		0..*	BackboneElement	Constant that can be used in FHIRPath expressions
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
name	C	1..1	string	Name of constant (referred to in FHIRPath as %[name]) sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
value[x]		1..1		Value of constant
valueBase64Binary			base64Binary
valueBoolean			boolean
valueCanonical			canonical(Any)
valueCode			code
valueDate			date
valueDateTime			dateTime
valueDecimal			decimal
valueId			id
valueInstant			instant
valueInteger			integer
valueInteger64			integer64
valueOid			oid
valueString			string
valuePositiveInt			positiveInt
valueTime			time
valueUnsignedInt			unsignedInt
valueUri			uri
valueUrl			url
valueUuid			uuid
select	C	1..*	BackboneElement	A collection of columns and nested selects to include in the view. sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
column	C	0..*	BackboneElement	A column to be produced in the resulting table. no-collections: Tabular view columns must not be collections. primitives-only: Tabular view columns only contain primitive values.
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
path		1..1	string	FHIRPath expression that creates a column and defines its content
name	C	1..1	string	Column name produced in the output sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
description		0..1	markdown	Description of the column
collection		0..1	boolean	Indicates whether the column may have multiple values.
type		0..1	uri	A FHIR StructureDefinition URI for the column's type.
tag		0..*	BackboneElement	Additional metadata describing the column
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
name		1..1	string	Name of tag
value		1..1	string	Value of tag
select		0..*	​See select (ViewDefinition)	Nested select relative to a parent expression.
(Choice of one)		0..1
forEach		0..1	string	A FHIRPath expression to retrieve the parent element(s) used in the containing select. The default is effectively `$this`.
forEachOrNull		0..1	string	Same as forEach, but will produce a row with null values if the collection is empty.
unionAll		0..*	​See select (ViewDefinition)	Creates a union of all rows in the given selection structures.
where		0..*	BackboneElement	A series of zero or more FHIRPath constraints to filter resources for the view.
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
path		1..1	string	A FHIRPath expression defining a filter condition
description		0..1	string	A human-readable description of the above where constraint.
Documentation for this format

Terminology Bindings

Path	Conformance	ValueSet	URI
ViewDefinition.status	required	PublicationStatus http://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard
ViewDefinition.resource	required	ResourceType http://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard
ViewDefinition.fhirVersion	required	FHIRVersion http://hl7.org/fhir/ValueSet/FHIR-version from the FHIR Standard

Constraints

Id	Grade	Path(s)	Details	Requirements
ele-1	error	**ALL** elements	All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count())
ext-1	error	**ALL** extensions	Must have either extensions or value[x], not both : extension.exists() != value.exists()
no-collections	error	ViewDefinition.select.column	Tabular view columns must not be collections. : collection.empty() or collection = false
primitives-only	error	ViewDefinition.select.column	Tabular view columns only contain primitive values. : type in ('base64Binary' | 'boolean' | 'canonical' | 'code' | 'dateTime' | 'decimal' | 'id' | 'instant' | 'integer' | 'integer64' | 'markdown' | 'oid' | 'string' | 'positiveInt' | 'time' | 'unsignedInt' | 'url' | 'uuid')
sql-expressions	error	ViewDefinition.select	Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1
sql-name	error	ViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.name	Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')

This structure is derived from ViewDefinition

Summary

Differential View

This structure is derived from ViewDefinition

Name	Flags	Card.	Type	Description & Constraints
ViewDefinition		0..*	ViewDefinition	Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where View Definition Instances of this logical model are not marked to be the target of a Reference
select
column	C	0..*	BackboneElement	A column to be produced in the resulting table. no-collections: Tabular view columns must not be collections. primitives-only: Tabular view columns only contain primitive values.
Documentation for this format

Constraints

Id	Grade	Path(s)	Details	Requirements
no-collections	error	ViewDefinition.select.column	Tabular view columns must not be collections. : collection.empty() or collection = false
primitives-only	error	ViewDefinition.select.column	Tabular view columns only contain primitive values. : type in ('base64Binary' | 'boolean' | 'canonical' | 'code' | 'dateTime' | 'decimal' | 'id' | 'instant' | 'integer' | 'integer64' | 'markdown' | 'oid' | 'string' | 'positiveInt' | 'time' | 'unsignedInt' | 'url' | 'uuid')

Key Elements View

Name	Flags	Card.	Type	Description & Constraints
ViewDefinition		0..*	ViewDefinition	Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where View Definition Instances of this logical model are not marked to be the target of a Reference
status		1..1	code	draft | active | retired | unknown Binding: PublicationStatus (required)
resource		1..1	code	FHIR resource for the ViewDefinition Binding: ResourceType (required)
select	C	1..*	BackboneElement	A collection of columns and nested selects to include in the view. sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
column	C	0..*	BackboneElement	A column to be produced in the resulting table. no-collections: Tabular view columns must not be collections. primitives-only: Tabular view columns only contain primitive values.
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
path		1..1	string	FHIRPath expression that creates a column and defines its content
name	C	1..1	string	Column name produced in the output sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
Documentation for this format

Terminology Bindings

Path	Conformance	ValueSet	URI
ViewDefinition.status	required	PublicationStatus http://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard
ViewDefinition.resource	required	ResourceType http://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard

Constraints

Id	Grade	Path(s)	Details	Requirements
ele-1	error	**ALL** elements	All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count())
ext-1	error	**ALL** extensions	Must have either extensions or value[x], not both : extension.exists() != value.exists()
no-collections	error	ViewDefinition.select.column	Tabular view columns must not be collections. : collection.empty() or collection = false
primitives-only	error	ViewDefinition.select.column	Tabular view columns only contain primitive values. : type in ('base64Binary' | 'boolean' | 'canonical' | 'code' | 'dateTime' | 'decimal' | 'id' | 'instant' | 'integer' | 'integer64' | 'markdown' | 'oid' | 'string' | 'positiveInt' | 'time' | 'unsignedInt' | 'url' | 'uuid')
sql-expressions	error	ViewDefinition.select	Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1
sql-name	error	ViewDefinition.select.column.name	Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')

Snapshot View

Name	Flags	Card.	Type	Description & Constraints
ViewDefinition		0..*	ViewDefinition	Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where View Definition Instances of this logical model are not marked to be the target of a Reference
url		0..1	uri	Canonical identifier for this view definition, represented as a URI (globally unique)
identifier		0..1	Identifier	Additional identifier for the view definition
name	C	0..1	string	Name of view definition (computer and database friendly) sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
title		0..1	string	Name for this view definition (human friendly)
meta		0..1	Meta	Metadata about the view definition
status		1..1	code	draft | active | retired | unknown Binding: PublicationStatus (required)
experimental		0..1	boolean	For testing purposes, not real usage
publisher		0..1	string	Name of the publisher/steward (organization or individual)
contact		0..*	ContactDetail	Contact details for the publisher
description		0..1	markdown	Natural language description of the view definition
useContext		0..*	UsageContext	The context that the content is intended to support
copyright		0..1	markdown	Use and/or publishing restrictions
resource		1..1	code	FHIR resource for the ViewDefinition Binding: ResourceType (required)
fhirVersion		0..*	code	FHIR version(s) of the resource for the ViewDefinition Binding: FHIRVersion (required)
constant		0..*	BackboneElement	Constant that can be used in FHIRPath expressions
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
name	C	1..1	string	Name of constant (referred to in FHIRPath as %[name]) sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
value[x]		1..1		Value of constant
valueBase64Binary			base64Binary
valueBoolean			boolean
valueCanonical			canonical(Any)
valueCode			code
valueDate			date
valueDateTime			dateTime
valueDecimal			decimal
valueId			id
valueInstant			instant
valueInteger			integer
valueInteger64			integer64
valueOid			oid
valueString			string
valuePositiveInt			positiveInt
valueTime			time
valueUnsignedInt			unsignedInt
valueUri			uri
valueUrl			url
valueUuid			uuid
select	C	1..*	BackboneElement	A collection of columns and nested selects to include in the view. sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
column	C	0..*	BackboneElement	A column to be produced in the resulting table. no-collections: Tabular view columns must not be collections. primitives-only: Tabular view columns only contain primitive values.
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
path		1..1	string	FHIRPath expression that creates a column and defines its content
name	C	1..1	string	Column name produced in the output sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
description		0..1	markdown	Description of the column
collection		0..1	boolean	Indicates whether the column may have multiple values.
type		0..1	uri	A FHIR StructureDefinition URI for the column's type.
tag		0..*	BackboneElement	Additional metadata describing the column
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
name		1..1	string	Name of tag
value		1..1	string	Value of tag
select		0..*	​See select (ViewDefinition)	Nested select relative to a parent expression.
(Choice of one)		0..1
forEach		0..1	string	A FHIRPath expression to retrieve the parent element(s) used in the containing select. The default is effectively `$this`.
forEachOrNull		0..1	string	Same as forEach, but will produce a row with null values if the collection is empty.
unionAll		0..*	​See select (ViewDefinition)	Creates a union of all rows in the given selection structures.
where		0..*	BackboneElement	A series of zero or more FHIRPath constraints to filter resources for the view.
@id		0..1	id	Unique id for inter-element referencing
extension		0..*	Extension	Additional content defined by implementations Slice: Unordered, Open by value:url
modifierExtension	?!Σ	0..*	Extension	Extensions that cannot be ignored even if unrecognized
path		1..1	string	A FHIRPath expression defining a filter condition
description		0..1	string	A human-readable description of the above where constraint.
Documentation for this format

Terminology Bindings

Path	Conformance	ValueSet	URI
ViewDefinition.status	required	PublicationStatus http://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard
ViewDefinition.resource	required	ResourceType http://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard
ViewDefinition.fhirVersion	required	FHIRVersion http://hl7.org/fhir/ValueSet/FHIR-version from the FHIR Standard

Constraints

Id	Grade	Path(s)	Details	Requirements
ele-1	error	**ALL** elements	All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count())
ext-1	error	**ALL** extensions	Must have either extensions or value[x], not both : extension.exists() != value.exists()
no-collections	error	ViewDefinition.select.column	Tabular view columns must not be collections. : collection.empty() or collection = false
primitives-only	error	ViewDefinition.select.column	Tabular view columns only contain primitive values. : type in ('base64Binary' | 'boolean' | 'canonical' | 'code' | 'dateTime' | 'decimal' | 'id' | 'instant' | 'integer' | 'integer64' | 'markdown' | 'oid' | 'string' | 'positiveInt' | 'time' | 'unsignedInt' | 'url' | 'uuid')
sql-expressions	error	ViewDefinition.select	Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1
sql-name	error	ViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.name	Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')

This structure is derived from ViewDefinition

Summary

Other representations of profile: CSV, Excel