Solved

MS SQL -- RegEx (or something)

Posted on 2012-03-16
10
506 Views
Last Modified: 2012-03-20
Change "select ExtensionSettings from Subscriptions"
so it only extracts "test3@test.com;test2@test.com"
on my 500+ results instead of getting the below ?

<ParameterValues><ParameterValue><Name>TO</Name><Value>test3@test.com;test2@test.com</Value></ParameterValue>
<ParameterValue><Name>ReplyTo</Name><Value>test@test.com</Value></ParameterValue>
<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>
<ParameterValue><Name>RenderFormat</Name><Value>EXCEL</Value></ParameterValue>
<ParameterValue><Name>Subject</Name><Value>EOM -- @ReportName</Value>
0
Comment
Question by:finance_teacher
10 Comments
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
select ExtensionSettings from Subscriptions where ExtensionSettings = 'test3@test.com;test2@test.com'
0
 

Author Comment

by:finance_teacher
Comment Utility
That get the entire ExtensionSettings column, I only want the a piece of it.

Like a SUBSTRING, but based on a REGEX (or something else).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Why not use Xml Data Type Methods for this?  That would be far more precise than using something like PATINDEX()
0
 

Author Comment

by:finance_teacher
Comment Utility
I read http://learnsqlserver.in/6/Xml-Data-Type-Methods.aspx, but have issues.

Please post an example solution.

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Here you go:
DECLARE @MyXml xml =
'<ParameterValues>
	<ParameterValue>
		<Name>TO</Name>
		<Value>test3@test.com;test2@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>ReplyTo</Name>
		<Value>test@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>IncludeReport</Name>
		<Value>True</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>RenderFormat</Name>
		<Value>EXCEL</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>Subject</Name>
		<Value>EOM -- @ReportName</Value>
	</ParameterValue>
</ParameterValues>'

SELECT	t.c.value('Value[1]', 'varchar(50)')
FROM	@MyXml.nodes('ParameterValues/ParameterValue') T(C)

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:finance_teacher
Comment Utility
My database table, subscriptions, has 500+ rows, how can I get your "DECLARE @MyXml xml..." to do something like "DECLARE @MyXml xml...select ExtensionSettings from Subscriptions..." ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Like this:
SELECT  s.*,
        t.c.value('Value[1]', 'varchar(50)') ExtensionSettings
FROM    subscriptions s
        CROSS APPLY s.MyXml.nodes('ParameterValues/ParameterValue') T (C)

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
Comment Utility
This is how I tested it:

DECLARE @subscriptions TABLE (MyXml xml)
INSERT @subscriptions (MyXml) VALUES (
'<ParameterValues>
	<ParameterValue>
		<Name>TO</Name>
		<Value>test3@test.com;test2@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>ReplyTo</Name>
		<Value>test@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>IncludeReport</Name>
		<Value>True</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>RenderFormat</Name>
		<Value>EXCEL</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>Subject</Name>
		<Value>EOM -- @ReportName</Value>
	</ParameterValue>
</ParameterValues>')

SELECT  s.*,
        t.c.value('Value[1]', 'varchar(50)') ExtensionSettings
FROM    @subscriptions s
        CROSS APPLY s.MyXml.nodes('ParameterValues/ParameterValue') T (C)

Open in new window

0
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 100 total points
Comment Utility
Credit to ACPerkins, but here is some code that should work with your SQL Report Server subscriptions table;

SELECT  Description, t.c.value('Value[1]', 'varchar(50)') ExtensionSettings        
FROM    
( select CAST(extensionsettings AS XML) ExtensionSettings, Description from subscriptions ) s
CROSS APPLY s.ExtensionSettings.nodes('/ParameterValues/ParameterValue') T (C)
WHERE t.c.value('Name[1]', 'varchar(50)') = 'TO'

Open in new window

0
 

Author Comment

by:finance_teacher
Comment Utility
Ok, I changed the below from "varchar(50)" to "varchar(500)" and it works.

SELECT  t.c.value('Value[1]', 'varchar(500)') ExtensionSettings        
FROM    
( select CAST(extensionsettings AS XML) ExtensionSettings, Description from subscriptions ) s
CROSS APPLY s.ExtensionSettings.nodes('/ParameterValues/ParameterValue') T (C)
WHERE t.c.value('Name[1]', 'varchar(500)') = 'TO'
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now