Retrieve XML Name Value Pair with T-SQL



Declare @s as NVarchar(2000) 
='<d><nv n="ParamID" v="Param/FYIInc./354088488" />
<nv n="ParamId" v="151af628-0000-0000-0000-000025700000" />
<nv n="ExtID" v="Station/Television" />
<nv n="StationId" v="0000357e-0000-0000-0000-000000587000" />
<nv n="StartTime" v="03/11/2014 02:01:00" />
<nv n="IsDynamic" v="True" />
<nv n="IsRecurring" v="True" />
<nv n="Frequency" v="EveryDay" />
<nv n="DurationSecs" v="0" /></d>'

--from a string
SELECT
    S.a.value('@n', 'varchar(50)') as name,
    S.a.value('@v', 'varchar(50)') as value   
FROM (SELECT Cast(@s as XML) as sXML) as t
CROSS APPLY sXML.nodes('d/nv') as S(a)


--from a table
declare @test table(id int identity(1,1), myString nvarchar(2000) );
INSERT INTO @test (myString) values(@s);

SELECT 
S.a.value('@n', 'varchar(50)') as name,
S.a.value('@v', 'varchar(50)') as value
FROM (SELECT Cast(myString as XML) as sXML 
FROM @test) t 
CROSS APPLY sXML.nodes('/d/nv') S(a)

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s