Skip to content

Instantly share code, notes, and snippets.

@shaneis
Created February 5, 2019 17:29
Show Gist options
  • Save shaneis/e5d597d6dc02a77038662d863b441adb to your computer and use it in GitHub Desktop.
Save shaneis/e5d597d6dc02a77038662d863b441adb to your computer and use it in GitHub Desktop.
DECLARE @XmlStoredAsNvarchar nvarchar(4000) = '
<EventContext>
<eventType>Save Test Data</eventType>
<discipline>Operations</discipline>
<documentNumber>1.2.3.4</documentNumber>
<documentVersion>1.0</documentVersion>
<sectionNumber>1.2.1.1</sectionNumber>
<sectionName>Test section: XML Test</sectionName>
<tableIdentifier>1</tableIdentifier>
<objectType>Object</objectType>
<objectTag>1.2.3.4</objectTag>
<rowIndex>10</rowIndex>
<testColumns>
<testColumn Index="3" Type="Choice List">
<columnName>Pass / Fail</columnName>
<oldValue />
<newValue>Pass</newValue>
</testColumn>
<testColumn Index="4" Type="Test Value">
<columnName>Bug #</columnName>
<oldValue> </oldValue>
<newValue> N/A</newValue>
</testColumn>
<testColumn Index="5" Type="Attachment">
<columnName>Attachment</columnName>
<oldValue>N/A</oldValue>
<newValue>N/A</newValue>
</testColumn>
<testColumn Index="6" Type="Issue">
<columnName>Deviation #</columnName>
<oldValue>N/A</oldValue>
<newValue>N/A</newValue>
</testColumn>
</testColumns>
<operatingMode>N/A</operatingMode>
</EventContext>';
SELECT
EventContexts.EventContext.value( '.', 'nvarchar(4000)' ) AS EverythingAndAll,
Disciplines.Discipline.value( '.', 'nvarchar(255)' ) AS Discipline,
NULL AS DocumentVersion,
NULL AS DocumentNumber,
NULL AS SectionNumber,
NULL AS SectionName
FROM (
SELECT
@XmlStoredAsNvarchar AS EverythingAndAll,
CAST(@XmlStoredAsNvarchar AS XML) AS DataColumn
) AS InnerData
OUTER APPLY InnerData.DataColumn.nodes('/EventContext') AS EventContexts ( EventContext )
OUTER APPLY InnerData.DataColumn.nodes('/EventContext/discipline') AS Disciplines ( Discipline )
OUTER APPLY InnerData.DataColumn.nodes('/EventContext/documentVersion') AS DocumentVersions ( DocumentVersion )
OUTER APPLY InnerData.DataColumn.nodes('/EventContext/documentNumber') AS DocumentNumbers ( DocumentNumber )
OUTER APPLY InnerData.DataColumn.nodes('/EventContext/sectionNumber') AS SectionNumbers ( SectionNumber )
OUTER APPLY InnerData.DataColumn.nodes('/EventContext/sectionName') AS SectionNames ( SectionName )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment