FunkySubstring.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
    *,
    SUBSTRING(X.DataColumn,PATINDEX('%<discipline>%',X.DataColumn)+12,PATINDEX('%</discipline>%',X.DataColumn)-PATINDEX('%<discipline>%',X.DataColumn)-12) AS Discipline,
    SUBSTRING(X.DataColumn,PATINDEX('%<DocumentVersion>%',X.DataColumn)+17,PATINDEX('%</DocumentVersion>%',X.DataColumn)-PATINDEX('%<DocumentVersion>%',X.DataColumn)-17) AS DocumentVersion,
    SUBSTRING(X.DataColumn,PATINDEX('%<DocumentNumber>%',X.DataColumn)+16,PATINDEX('%</DocumentNumber>%',X.DataColumn)-PATINDEX('%<DocumentNumber>%',X.DataColumn)-16) AS DocumentNumber,
    SUBSTRING(X.DataColumn,PATINDEX('%<SectionNumber>%',X.DataColumn)+15,PATINDEX('%</SectionNumber>%',X.DataColumn)-PATINDEX('%<SectionNumber>%',X.DataColumn)-15) AS SectionNumber,
    SUBSTRING(X.DataColumn,PATINDEX('%<SectionName>%',X.DataColumn)+13,PATINDEX('%</SectionName>%',X.DataColumn)-PATINDEX('%<SectionName>%',X.DataColumn)-13) AS SectionName
FROM (
    SELECT @XmlStoredAsNvarchar AS DataColumn
) AS X (DataColumn);