SQL on FHIR
2.0.0 - release International flag

This page is part of the SQL on FHIR (v2.0.0: Release) based on FHIR (HL7® FHIR® Standard) v5.0.0. This is the current published version. For a full list of available versions, see the Directory of published versions

Logical Model: Tabular View Definition

Official URL: https://sql-on-fhir.org/ig/StructureDefinition/TabularViewDefinition Version: 2.0.0
Draft as of 2024-10-09 Computable Name: TabularViewDefinition

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

This structure is derived from ViewDefinition

NameFlagsCard.TypeDescription & Constraintsdoco
.. 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.

doco Documentation for this format

Constraints

IdGradePath(s)DetailsRequirements
no-collectionserrorViewDefinition.select.columnTabular view columns must not be collections.
: collection.empty() or collection = false
primitives-onlyerrorViewDefinition.select.columnTabular 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')
NameFlagsCard.TypeDescription & Constraintsdoco
.. 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.

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
no-collectionserrorViewDefinition.select.columnTabular view columns must not be collections.
: collection.empty() or collection = false
primitives-onlyerrorViewDefinition.select.columnTabular 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-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.select.column.nameName 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_]*$')
NameFlagsCard.TypeDescription & Constraintsdoco
.. 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.
... 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.

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard
ViewDefinition.fhirVersionrequiredFHIRVersion
http://hl7.org/fhir/ValueSet/FHIR-version
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
no-collectionserrorViewDefinition.select.columnTabular view columns must not be collections.
: collection.empty() or collection = false
primitives-onlyerrorViewDefinition.select.columnTabular 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-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.nameName 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

NameFlagsCard.TypeDescription & Constraintsdoco
.. 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.

doco Documentation for this format

Constraints

IdGradePath(s)DetailsRequirements
no-collectionserrorViewDefinition.select.columnTabular view columns must not be collections.
: collection.empty() or collection = false
primitives-onlyerrorViewDefinition.select.columnTabular 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

NameFlagsCard.TypeDescription & Constraintsdoco
.. 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.

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
no-collectionserrorViewDefinition.select.columnTabular view columns must not be collections.
: collection.empty() or collection = false
primitives-onlyerrorViewDefinition.select.columnTabular 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-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.select.column.nameName 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

NameFlagsCard.TypeDescription & Constraintsdoco
.. 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.
... 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.

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard
ViewDefinition.fhirVersionrequiredFHIRVersion
http://hl7.org/fhir/ValueSet/FHIR-version
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
no-collectionserrorViewDefinition.select.columnTabular view columns must not be collections.
: collection.empty() or collection = false
primitives-onlyerrorViewDefinition.select.columnTabular 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-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.nameName 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