StartingRegex.sql

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 )