Skip to content

Instantly share code, notes, and snippets.

@mjul

mjul/SQLxmlquery

Created September 24, 2013 14:41
Show Gist options
  • Save mjul/6685770 to your computer and use it in GitHub Desktop.
Save mjul/6685770 to your computer and use it in GitHub Desktop.
Download ZIP
From an XML document in SQL Server, extract the value of an XML node via an XML query. For example, to extract the TransactionId value as a Guid from the Metadata node on an XML document, do this:
Raw
exampleevent
<FooHappened>
<Metadata xmlns:d2p1="http://schemas.datacontract.org/2004/07/FooSchema">
<d2p1:TransactionId>95a2e035-260b-4806-920b-e584878414e0</d2p1:TransactionId>
</Metadata>
</FooHappened>
Raw
SQLxmlquery
SELECT
[Id]
,cast([EventData] as xml)
.value('(/*/*[local-name() = "Metadata"]/*[local-name() = "TransactionId"])[1]', 'uniqueidentifier') as txid
FROM [Playground].[command].[Events]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment