Skip to content

Instantly share code, notes, and snippets.

@hancheester

hancheester/get-text-from-xml-element.sql

Last active November 19, 2019 10:31
Show Gist options
  • Save hancheester/75e52c11c210581d6767c1de8514a859 to your computer and use it in GitHub Desktop.
Save hancheester/75e52c11c210581d6767c1de8514a859 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
get-text-from-xml-element.sql
-- Create table variable
declare @Temp as table
(
ID int not null primary key,
Data xml not null
)
-- Populate date into table variable
insert into @Temp (ID, Data)
select 1, '<Root xmlns:ns1="http://www.ns1.com/2010/1">
<ns1:NS1 xmlns:ext="http://www.ns1.com/2010/1/extension">
<ns1:Header>NS1 Header</ns1:Header>
<ext:Header>EXT Header</ext:Header>
<ext:Footer>EXT Footer</ext:Footer>
<ns1:Footer>NS1 Footer</ns1:Footer>
</ns1:NS1>
</Root>'
-- Select text from particular xml element
select ID, Data.value('
declare namespace ns1="http://www.ns1.com/2010/1";
declare namespace ext="http://www.ns1.com/2010/1/extension";
(/Root/ns1:NS1/ext:Header)[1]', 'nvarchar(max)') extHeader
from @Temp
-- Filter by text
select * from @Temp
where Data.exist('//*[text()[contains(.,"Header 2")]]') = 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment