Skip to content

Instantly share code, notes, and snippets.

@shaneis
Created February 5, 2019 14:37
Show Gist options
  • Save shaneis/47d0705638e27f7efd0a547905cc5895 to your computer and use it in GitHub Desktop.
Save shaneis/47d0705638e27f7efd0a547905cc5895 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
*,
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment