How to parse simple XML strings in SQLServer using T-SQL

Assume you have a Simple XML String which has the following structure

‘<Filters>

<Filter GroupName=”NSPC Region1″ GroupMemberName=”90-WESTERN1″/>

<Filter GroupName=”NSPC Region1″ GroupMemberName=”91-WESTERN1″/>

<Filter GroupName=”NSPC Region1″ GroupMemberName=”92-WESTERN1″/>

<Filter GroupName=”NSPC Region2″ GroupMemberName=”90-WESTERN2″/>

<Filter GroupName=”NSPC Region2″ GroupMemberName=”91-WESTERN2″/>

<Filter GroupName=”NSPC Region2″ GroupMemberName=”92-WESTERN2″/>

</Filters>’

And you would like to parse out the Outer and Inner Attributes – here is some simple code to do that in T-SQL

Declare @SegmentGroupFilters xml

DECLARE @FilterSGDisplayName varchar(255)

DECLARE @FilterSEGDisplayname varchar(255)

set @SegmentGroupFilters =    ‘<Filters>

<Filter GroupName=”NSPC Region1″ GroupMemberName=”90-WESTERN1″/>

<Filter GroupName=”NSPC Region1″ GroupMemberName=”91-WESTERN1″/>

<Filter GroupName=”NSPC Region1″ GroupMemberName=”92-WESTERN1″/>

<Filter GroupName=”NSPC Region2″ GroupMemberName=”90-WESTERN2″/>

<Filter GroupName=”NSPC Region2″ GroupMemberName=”91-WESTERN2″/>

<Filter GroupName=”NSPC Region2″ GroupMemberName=”92-WESTERN2″/>

</Filters>’

if ( @SegmentGroupFilters is not null  )

BEGIN

print ‘@SegmentGroupFilters is not null’

DECLARE sg_cursor2 CURSOR FOR

SELECT

T1.rows.value(‘@GroupName’,’varchar(100)’) as FilterSGDisplayName,

T1.rows.value(‘@GroupMemberName’,’varchar(100)’) as FilterSegmentDisplayName

FROM @SegmentGroupFilters.nodes(‘/Filters/Filter’) T1(rows)

OPEN sg_cursor2

— Read first row from config table

FETCH NEXT

FROM sg_cursor2

INTO @FilterSGDisplayName, @FilterSEGDisplayname

WHILE @@FETCH_STATUS = 0

BEGIN

print ‘GroupName ‘ + @FilterSGDisplayName

print ‘GroupMemberName ‘ + @FilterSEGDisplayname

FETCH NEXT

FROM sg_cursor2

INTO @FilterSGDisplayName, @FilterSEGDisplayname

END

CLOSE sg_cursor2

DEALLOCATE sg_cursor2

END

Results

@SegmentGroupFilters is not null

GroupName NSPC Region1

GroupMemberName 90-WESTERN1

GroupName NSPC Region1

GroupMemberName 91-WESTERN1

GroupName NSPC Region1

GroupMemberName 92-WESTERN1

GroupName NSPC Region2

GroupMemberName 90-WESTERN2

GroupName NSPC Region2

GroupMemberName 91-WESTERN2

GroupName NSPC Region2

GroupMemberName 92-WESTERN2

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