|
|
|
date: Thu, 3 Jul 2008 13:11:03 -0700 (PDT),
group: microsoft.public.sqlserver.xml
back
newbie to xpath/xquery trying to pull email address out of xml in
SSRS subscription table
i'm trying to pull out email addresses info from the SSRS subscription
table. The xml looks like this:
<ParameterValues>
<ParameterValue>
<Name>TO</Name>
<Value>pep1@myemail.com;pep2@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>BCC</Name>
<Value>pep3@myemail.com;pep4@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>ReplyTo</Name>
<Value>pep5@myemail.com;pep6@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value></ParameterValue>
<ParameterValue>
<Name>RenderFormat</Name>
<Value>EXCEL</Value>
</ParameterValue>
<ParameterValue>
<Name>Subject</Name>
<Value>@ReportName was executed at @ExecutionTime</Value>
</ParameterValue>
<ParameterValue>
<Name>Priority</Name>
<Value>NORMAL</Value>
</ParameterValue></ParameterValues>
</ParameterValues>
I have not been able to figure out the correct syntax with xpath or
xquery...my logic is as follows:
pull the 'value' data when it's sibling <Name> value = "TO"
I've done the following:
SELECT SubscriptionID, extensionSettings,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
'varchar(max)') AS col1,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
'varchar(max)') AS col2,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
'varchar(max)') AS col3
from Subscriptions
which gets me the To, CC, & BCC data into output columns 1,2,3. but I
cannot assume the 2nd value is always of 'CC' type...
I've also tried:
SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
="TO"]/Value)','varchar(max)') AS col1
from Subscriptions
but this generates an error about " 'value()' requires a
singleton...."
anyone provide an example of how to accomplish this?
thanks.
date: Thu, 3 Jul 2008 13:11:03 -0700 (PDT)
author: unknown
RE: newbie to xpath/xquery trying to pull email address out of xml in
Try this:
DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
extensionSettings XML )
DECLARE @xml XML
INSERT INTO @subscriptions VALUES ( '
<ParameterValues>
<ParameterValue>
<Name>TO</Name>
<Value>pep1@myemail.com;pep2@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>BCC</Name>
<Value>pep3@myemail.com;pep4@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>ReplyTo</Name>
<Value>pep5@myemail.com;pep6@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value></ParameterValue>
<ParameterValue>
<Name>RenderFormat</Name>
<Value>EXCEL</Value>
</ParameterValue>
<ParameterValue>
<Name>Subject</Name>
<Value>@ReportName was executed at @ExecutionTime</Value>
</ParameterValue>
<ParameterValue>
<Name>Priority</Name>
<Value>NORMAL</Value>
</ParameterValue>
</ParameterValues>' )
-- pull the 'value' data when it's sibling <Name> value = "TO"
SELECT
--c.d.value('local-name', 'VARCHAR(30)'),
e.a.value('.', 'VARCHAR(30)') x,
-- OR
e.a.value('(text())[1]', 'VARCHAR(30)') y
FROM @subscriptions
CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
"BCC" or Name = "ReplyTo"]/Value') e(a)
HTH
wBob
"kent.eilers@res-direct.com" wrote:
> i'm trying to pull out email addresses info from the SSRS subscription
> table. The xml looks like this:
>
> <ParameterValues>
> <ParameterValue>
> <Name>TO</Name>
> <Value>pep1@myemail.com;pep2@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>BCC</Name>
> <Value>pep3@myemail.com;pep4@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>ReplyTo</Name>
> <Value>pep5@myemail.com;pep6@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>IncludeReport</Name>
> <Value>True</Value></ParameterValue>
> <ParameterValue>
> <Name>RenderFormat</Name>
> <Value>EXCEL</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Subject</Name>
> <Value>@ReportName was executed at @ExecutionTime</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Priority</Name>
> <Value>NORMAL</Value>
> </ParameterValue></ParameterValues>
> </ParameterValues>
>
>
> I have not been able to figure out the correct syntax with xpath or
> xquery...my logic is as follows:
>
> pull the 'value' data when it's sibling <Name> value = "TO"
>
> I've done the following:
>
> SELECT SubscriptionID, extensionSettings,
> CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
> 'varchar(max)') AS col1,
> CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
> 'varchar(max)') AS col2,
> CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
> 'varchar(max)') AS col3
> from Subscriptions
>
> which gets me the To, CC, & BCC data into output columns 1,2,3. but I
> cannot assume the 2nd value is always of 'CC' type...
>
> I've also tried:
> SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
> ="TO"]/Value)','varchar(max)') AS col1
> from Subscriptions
>
> but this generates an error about " 'value()' requires a
> singleton...."
>
> anyone provide an example of how to accomplish this?
>
> thanks.
>
date: Thu, 3 Jul 2008 14:43:01 -0700
author: Bob
RE: newbie to xpath/xquery trying to pull email address out of xml
And shred it. And use a longer VARCHAR ...
DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
extensionSettings XML )
DECLARE @xml XML
INSERT INTO @subscriptions VALUES ( '
<ParameterValues>
<ParameterValue>
<Name>TO</Name>
<Value>pep1@myemail.com;pep2@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>BCC</Name>
<Value>pep3@myemail.com;pep4@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>ReplyTo</Name>
<Value>pep5@myemail.com;pep6@myemail.com</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value></ParameterValue>
<ParameterValue>
<Name>RenderFormat</Name>
<Value>EXCEL</Value>
</ParameterValue>
<ParameterValue>
<Name>Subject</Name>
<Value>@ReportName was executed at @ExecutionTime</Value>
</ParameterValue>
<ParameterValue>
<Name>Priority</Name>
<Value>NORMAL</Value>
</ParameterValue>
</ParameterValues>' )
SELECT e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
FROM @subscriptions
CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
"BCC" or Name = "ReplyTo"]') e(a)
DECLARE @emails TABLE ( email XML )
INSERT INTO @emails ( email )
SELECT '<email>' + REPLACE( y, ';', '</email><email>' ) + '</email>' email
FROM
(
SELECT
e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
FROM @subscriptions
CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
"BCC" or Name = "ReplyTo"]') e(a)
) x
-- And shred it
SELECT x.y.value('.', 'VARCHAR(50)')
FROM @emails
CROSS APPLY email.nodes('email') x(y)
"Bob" wrote:
> Try this:
>
> DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> extensionSettings XML )
>
> DECLARE @xml XML
>
> INSERT INTO @subscriptions VALUES ( '
> <ParameterValues>
> <ParameterValue>
> <Name>TO</Name>
> <Value>pep1@myemail.com;pep2@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>BCC</Name>
> <Value>pep3@myemail.com;pep4@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>ReplyTo</Name>
> <Value>pep5@myemail.com;pep6@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>IncludeReport</Name>
> <Value>True</Value></ParameterValue>
> <ParameterValue>
> <Name>RenderFormat</Name>
> <Value>EXCEL</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Subject</Name>
> <Value>@ReportName was executed at @ExecutionTime</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Priority</Name>
> <Value>NORMAL</Value>
> </ParameterValue>
> </ParameterValues>' )
>
> -- pull the 'value' data when it's sibling <Name> value = "TO"
> SELECT
> --c.d.value('local-name', 'VARCHAR(30)'),
> e.a.value('.', 'VARCHAR(30)') x,
> -- OR
> e.a.value('(text())[1]', 'VARCHAR(30)') y
>
> FROM @subscriptions
> CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> "BCC" or Name = "ReplyTo"]/Value') e(a)
>
> HTH
> wBob
>
>
> "kent.eilers@res-direct.com" wrote:
>
> > i'm trying to pull out email addresses info from the SSRS subscription
> > table. The xml looks like this:
> >
> > <ParameterValues>
> > <ParameterValue>
> > <Name>TO</Name>
> > <Value>pep1@myemail.com;pep2@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>BCC</Name>
> > <Value>pep3@myemail.com;pep4@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>ReplyTo</Name>
> > <Value>pep5@myemail.com;pep6@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>IncludeReport</Name>
> > <Value>True</Value></ParameterValue>
> > <ParameterValue>
> > <Name>RenderFormat</Name>
> > <Value>EXCEL</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Subject</Name>
> > <Value>@ReportName was executed at @ExecutionTime</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Priority</Name>
> > <Value>NORMAL</Value>
> > </ParameterValue></ParameterValues>
> > </ParameterValues>
> >
> >
> > I have not been able to figure out the correct syntax with xpath or
> > xquery...my logic is as follows:
> >
> > pull the 'value' data when it's sibling <Name> value = "TO"
> >
> > I've done the following:
> >
> > SELECT SubscriptionID, extensionSettings,
> > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
> > 'varchar(max)') AS col1,
> > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
> > 'varchar(max)') AS col2,
> > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
> > 'varchar(max)') AS col3
> > from Subscriptions
> >
> > which gets me the To, CC, & BCC data into output columns 1,2,3. but I
> > cannot assume the 2nd value is always of 'CC' type...
> >
> > I've also tried:
> > SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
> > ="TO"]/Value)','varchar(max)') AS col1
> > from Subscriptions
> >
> > but this generates an error about " 'value()' requires a
> > singleton...."
> >
> > anyone provide an example of how to accomplish this?
> >
> > thanks.
> >
date: Thu, 3 Jul 2008 15:53:00 -0700
author: Bob
Re: newbie to xpath/xquery trying to pull email address out of xml
On Jul 3, 5:53 pm, Bob wrote:
> And shred it. And use a longer VARCHAR ...
>
> DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> extensionSettings XML )
>
> DECLARE @xml XML
>
> INSERT INTO @subscriptions VALUES ( '
> <ParameterValues>
> <ParameterValue>
> <Name>TO</Name>
> <Value>p...@myemail.com;p...@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>BCC</Name>
> <Value>p...@myemail.com;p...@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>ReplyTo</Name>
> <Value>p...@myemail.com;p...@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>IncludeReport</Name>
> <Value>True</Value></ParameterValue>
> <ParameterValue>
> <Name>RenderFormat</Name>
> <Value>EXCEL</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Subject</Name>
> <Value>@ReportName was executed at @ExecutionTime</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Priority</Name>
> <Value>NORMAL</Value>
> </ParameterValue>
> </ParameterValues>' )
>
> SELECT e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> FROM @subscriptions
> CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> "BCC" or Name = "ReplyTo"]') e(a)
>
> DECLARE @emails TABLE ( email XML )
>
> INSERT INTO @emails ( email )
> SELECT '<email>' REPLACE( y, ';', '</email><email>' ) '</email>' email
> FROM
> (
> SELECT
> e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> FROM @subscriptions
> CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> "BCC" or Name = "ReplyTo"]') e(a)
> ) x
>
> -- And shred it
> SELECT x.y.value('.', 'VARCHAR(50)')
> FROM @emails
> CROSS APPLY email.nodes('email') x(y)
>
> "Bob" wrote:
> > Try this:
>
> > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> > extensionSettings XML )
>
> > DECLARE @xml XML
>
> > INSERT INTO @subscriptions VALUES ( '
> > <ParameterValues>
> > <ParameterValue>
> > <Name>TO</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>BCC</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>ReplyTo</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>IncludeReport</Name>
> > <Value>True</Value></ParameterValue>
> > <ParameterValue>
> > <Name>RenderFormat</Name>
> > <Value>EXCEL</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Subject</Name>
> > <Value>@ReportName was executed at @ExecutionTime</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Priority</Name>
> > <Value>NORMAL</Value>
> > </ParameterValue>
> > </ParameterValues>' )
>
> > -- pull the 'value' data when it's sibling <Name> value = "TO"
> > SELECT
> > --c.d.value('local-name', 'VARCHAR(30)'),
> > e.a.value('.', 'VARCHAR(30)') x,
> > -- OR
> > e.a.value('(text())[1]', 'VARCHAR(30)') y
>
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > "BCC" or Name = "ReplyTo"]/Value') e(a)
>
> > HTH
> > wBob
>
> > "kent.eil...@res-direct.com" wrote:
>
> > > i'm trying to pull out email addresses info from the SSRS subscription
> > > table. The xml looks like this:
>
> > > <ParameterValues>
> > > <ParameterValue>
> > > <Name>TO</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>BCC</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>ReplyTo</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>IncludeReport</Name>
> > > <Value>True</Value></ParameterValue>
> > > <ParameterValue>
> > > <Name>RenderFormat</Name>
> > > <Value>EXCEL</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Subject</Name>
> > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Priority</Name>
> > > <Value>NORMAL</Value>
> > > </ParameterValue></ParameterValues>
> > > </ParameterValues>
>
> > > I have not been able to figure out the correct syntax with xpath or
> > > xquery...my logic is as follows:
>
> > > pull the 'value' data when it's sibling <Name> value = "TO"
>
> > > I've done the following:
>
> > > SELECT SubscriptionID, extensionSettings,
> > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
> > > 'varchar(max)') AS col1,
> > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
> > > 'varchar(max)') AS col2,
> > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
> > > 'varchar(max)') AS col3
> > > from Subscriptions
>
> > > which gets me the To, CC, & BCC data into output columns 1,2,3. but I
> > > cannot assume the 2nd value is always of 'CC' type...
>
> > > I've also tried:
> > > SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
> > > ="TO"]/Value)','varchar(max)') AS col1
> > > from Subscriptions
>
> > > but this generates an error about " 'value()' requires a
> > > singleton...."
>
> > > anyone provide an example of how to accomplish this?
>
> > > thanks.
wow - it works too!
very nice...i'll have to look at this carefully..
Thanks !
date: Mon, 7 Jul 2008 05:57:26 -0700 (PDT)
author: unknown
Re: newbie to xpath/xquery trying to pull email address out of xml
On Jul 3, 5:53 pm, Bob wrote:
> And shred it. And use a longer VARCHAR ...
>
> DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> extensionSettings XML )
>
> DECLARE @xml XML
>
> INSERT INTO @subscriptions VALUES ( '
> <ParameterValues>
> <ParameterValue>
> <Name>TO</Name>
> <Value>p...@myemail.com;p...@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>BCC</Name>
> <Value>p...@myemail.com;p...@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>ReplyTo</Name>
> <Value>p...@myemail.com;p...@myemail.com</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>IncludeReport</Name>
> <Value>True</Value></ParameterValue>
> <ParameterValue>
> <Name>RenderFormat</Name>
> <Value>EXCEL</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Subject</Name>
> <Value>@ReportName was executed at @ExecutionTime</Value>
> </ParameterValue>
> <ParameterValue>
> <Name>Priority</Name>
> <Value>NORMAL</Value>
> </ParameterValue>
> </ParameterValues>' )
>
> SELECT e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> FROM @subscriptions
> CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> "BCC" or Name = "ReplyTo"]') e(a)
>
> DECLARE @emails TABLE ( email XML )
>
> INSERT INTO @emails ( email )
> SELECT '<email>' REPLACE( y, ';', '</email><email>' ) '</email>' email
> FROM
> (
> SELECT
> e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> FROM @subscriptions
> CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> "BCC" or Name = "ReplyTo"]') e(a)
> ) x
>
> -- And shred it
> SELECT x.y.value('.', 'VARCHAR(50)')
> FROM @emails
> CROSS APPLY email.nodes('email') x(y)
>
> "Bob" wrote:
> > Try this:
>
> > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> > extensionSettings XML )
>
> > DECLARE @xml XML
>
> > INSERT INTO @subscriptions VALUES ( '
> > <ParameterValues>
> > <ParameterValue>
> > <Name>TO</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>BCC</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>ReplyTo</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>IncludeReport</Name>
> > <Value>True</Value></ParameterValue>
> > <ParameterValue>
> > <Name>RenderFormat</Name>
> > <Value>EXCEL</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Subject</Name>
> > <Value>@ReportName was executed at @ExecutionTime</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Priority</Name>
> > <Value>NORMAL</Value>
> > </ParameterValue>
> > </ParameterValues>' )
>
> > -- pull the 'value' data when it's sibling <Name> value = "TO"
> > SELECT
> > --c.d.value('local-name', 'VARCHAR(30)'),
> > e.a.value('.', 'VARCHAR(30)') x,
> > -- OR
> > e.a.value('(text())[1]', 'VARCHAR(30)') y
>
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > "BCC" or Name = "ReplyTo"]/Value') e(a)
>
> > HTH
> > wBob
>
> > "kent.eil...@res-direct.com" wrote:
>
> > > i'm trying to pull out email addresses info from the SSRS subscription
> > > table. The xml looks like this:
>
> > > <ParameterValues>
> > > <ParameterValue>
> > > <Name>TO</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>BCC</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>ReplyTo</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>IncludeReport</Name>
> > > <Value>True</Value></ParameterValue>
> > > <ParameterValue>
> > > <Name>RenderFormat</Name>
> > > <Value>EXCEL</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Subject</Name>
> > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Priority</Name>
> > > <Value>NORMAL</Value>
> > > </ParameterValue></ParameterValues>
> > > </ParameterValues>
>
> > > I have not been able to figure out the correct syntax with xpath or
> > > xquery...my logic is as follows:
>
> > > pull the 'value' data when it's sibling <Name> value = "TO"
>
> > > I've done the following:
>
> > > SELECT SubscriptionID, extensionSettings,
> > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
> > > 'varchar(max)') AS col1,
> > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
> > > 'varchar(max)') AS col2,
> > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
> > > 'varchar(max)') AS col3
> > > from Subscriptions
>
> > > which gets me the To, CC, & BCC data into output columns 1,2,3. but I
> > > cannot assume the 2nd value is always of 'CC' type...
>
> > > I've also tried:
> > > SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
> > > ="TO"]/Value)','varchar(max)') AS col1
> > > from Subscriptions
>
> > > but this generates an error about " 'value()' requires a
> > > singleton...."
>
> > > anyone provide an example of how to accomplish this?
>
> > > thanks.
Bob - where is the "extensionSettings.nodes" function documented?
Thanks
date: Tue, 8 Jul 2008 06:02:07 -0700 (PDT)
author: unknown
Re: newbie to xpath/xquery trying to pull email address out of xml
New in SQL2005, the xml datatype has methods you can use against it. Start
in Books Online (BOL) here:
XQuery Against the xml Data Type
"kent.eilers@res-direct.com" wrote:
> On Jul 3, 5:53 pm, Bob wrote:
> > And shred it. And use a longer VARCHAR ...
> >
> > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> > extensionSettings XML )
> >
> > DECLARE @xml XML
> >
> > INSERT INTO @subscriptions VALUES ( '
> > <ParameterValues>
> > <ParameterValue>
> > <Name>TO</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>BCC</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>ReplyTo</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>IncludeReport</Name>
> > <Value>True</Value></ParameterValue>
> > <ParameterValue>
> > <Name>RenderFormat</Name>
> > <Value>EXCEL</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Subject</Name>
> > <Value>@ReportName was executed at @ExecutionTime</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Priority</Name>
> > <Value>NORMAL</Value>
> > </ParameterValue>
> > </ParameterValues>' )
> >
> > SELECT e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > "BCC" or Name = "ReplyTo"]') e(a)
> >
> > DECLARE @emails TABLE ( email XML )
> >
> > INSERT INTO @emails ( email )
> > SELECT '<email>' + REPLACE( y, ';', '</email><email>' ) + '</email>' email
> > FROM
> > (
> > SELECT
> > e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > "BCC" or Name = "ReplyTo"]') e(a)
> > ) x
> >
> > -- And shred it
> > SELECT x.y.value('.', 'VARCHAR(50)')
> > FROM @emails
> > CROSS APPLY email.nodes('email') x(y)
> >
> > "Bob" wrote:
> > > Try this:
> >
> > > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> > > extensionSettings XML )
> >
> > > DECLARE @xml XML
> >
> > > INSERT INTO @subscriptions VALUES ( '
> > > <ParameterValues>
> > > <ParameterValue>
> > > <Name>TO</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>BCC</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>ReplyTo</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>IncludeReport</Name>
> > > <Value>True</Value></ParameterValue>
> > > <ParameterValue>
> > > <Name>RenderFormat</Name>
> > > <Value>EXCEL</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Subject</Name>
> > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Priority</Name>
> > > <Value>NORMAL</Value>
> > > </ParameterValue>
> > > </ParameterValues>' )
> >
> > > -- pull the 'value' data when it's sibling <Name> value = "TO"
> > > SELECT
> > > --c.d.value('local-name', 'VARCHAR(30)'),
> > > e.a.value('.', 'VARCHAR(30)') x,
> > > -- OR
> > > e.a.value('(text())[1]', 'VARCHAR(30)') y
> >
> > > FROM @subscriptions
> > > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > > "BCC" or Name = "ReplyTo"]/Value') e(a)
> >
> > > HTH
> > > wBob
> >
> > > "kent.eil...@res-direct.com" wrote:
> >
> > > > i'm trying to pull out email addresses info from the SSRS subscription
> > > > table. The xml looks like this:
> >
> > > > <ParameterValues>
> > > > <ParameterValue>
> > > > <Name>TO</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>BCC</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>ReplyTo</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>IncludeReport</Name>
> > > > <Value>True</Value></ParameterValue>
> > > > <ParameterValue>
> > > > <Name>RenderFormat</Name>
> > > > <Value>EXCEL</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>Subject</Name>
> > > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>Priority</Name>
> > > > <Value>NORMAL</Value>
> > > > </ParameterValue></ParameterValues>
> > > > </ParameterValues>
> >
> > > > I have not been able to figure out the correct syntax with xpath or
> > > > xquery...my logic is as follows:
> >
> > > > pull the 'value' data when it's sibling <Name> value = "TO"
> >
> > > > I've done the following:
> >
> > > > SELECT SubscriptionID, extensionSettings,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
> > > > 'varchar(max)') AS col1,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
> > > > 'varchar(max)') AS col2,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
> > > > 'varchar(max)') AS col3
> > > > from Subscriptions
> >
> > > > which gets me the To, CC, & BCC data into output columns 1,2,3. but I
> > > > cannot assume the 2nd value is always of 'CC' type...
> >
> > > > I've also tried:
> > > > SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
> > > > ="TO"]/Value)','varchar(max)') AS col1
> > > > from Subscriptions
> >
> > > > but this generates an error about " 'value()' requires a
> > > > singleton...."
> >
> > > > anyone provide an example of how to accomplish this?
> >
> > > > thanks.
>
> Bob - where is the "extensionSettings.nodes" function documented?
>
> Thanks
>
date: Tue, 8 Jul 2008 06:56:09 -0700
author: Bob
Re: newbie to xpath/xquery trying to pull email address out of xml
On Jul 7, 7:57 am, kent.eil...@res-direct.com wrote:
> On Jul 3, 5:53 pm, Bob wrote:
>
>
>
> > And shred it. And use a longer VARCHAR ...
>
> > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> > extensionSettings XML )
>
> > DECLARE @xml XML
>
> > INSERT INTO @subscriptions VALUES ( '
> > <ParameterValues>
> > <ParameterValue>
> > <Name>TO</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>BCC</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>ReplyTo</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>IncludeReport</Name>
> > <Value>True</Value></ParameterValue>
> > <ParameterValue>
> > <Name>RenderFormat</Name>
> > <Value>EXCEL</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Subject</Name>
> > <Value>@ReportName was executed at @ExecutionTime</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Priority</Name>
> > <Value>NORMAL</Value>
> > </ParameterValue>
> > </ParameterValues>' )
>
> > SELECT e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > "BCC" or Name = "ReplyTo"]') e(a)
>
> > DECLARE @emails TABLE ( email XML )
>
> > INSERT INTO @emails ( email )
> > SELECT '<email>' REPLACE( y, ';', '</email><email>' ) '</email>' email
> > FROM
> > (
> > SELECT
> > e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > "BCC" or Name = "ReplyTo"]') e(a)
> > ) x
>
> > -- And shred it
> > SELECT x.y.value('.', 'VARCHAR(50)')
> > FROM @emails
> > CROSS APPLY email.nodes('email') x(y)
>
> > "Bob" wrote:
> > > Try this:
>
> > > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> > > extensionSettings XML )
>
> > > DECLARE @xml XML
>
> > > INSERT INTO @subscriptions VALUES ( '
> > > <ParameterValues>
> > > <ParameterValue>
> > > <Name>TO</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>BCC</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>ReplyTo</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>IncludeReport</Name>
> > > <Value>True</Value></ParameterValue>
> > > <ParameterValue>
> > > <Name>RenderFormat</Name>
> > > <Value>EXCEL</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Subject</Name>
> > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Priority</Name>
> > > <Value>NORMAL</Value>
> > > </ParameterValue>
> > > </ParameterValues>' )
>
> > > -- pull the 'value' data when it's sibling <Name> value = "TO"
> > > SELECT
> > > --c.d.value('local-name', 'VARCHAR(30)'),
> > > e.a.value('.', 'VARCHAR(30)') x,
> > > -- OR
> > > e.a.value('(text())[1]', 'VARCHAR(30)') y
>
> > > FROM @subscriptions
> > > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or Name =
> > > "BCC" or Name = "ReplyTo"]/Value') e(a)
>
> > > HTH
> > > wBob
>
> > > "kent.eil...@res-direct.com" wrote:
>
> > > > i'm trying to pull out email addresses info from the SSRS subscription
> > > > table. The xml looks like this:
>
> > > > <ParameterValues>
> > > > <ParameterValue>
> > > > <Name>TO</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>BCC</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>ReplyTo</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>IncludeReport</Name>
> > > > <Value>True</Value></ParameterValue>
> > > > <ParameterValue>
> > > > <Name>RenderFormat</Name>
> > > > <Value>EXCEL</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>Subject</Name>
> > > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>Priority</Name>
> > > > <Value>NORMAL</Value>
> > > > </ParameterValue></ParameterValues>
> > > > </ParameterValues>
>
> > > > I have not been able to figure out the correct syntax with xpath or
> > > > xquery...my logic is as follows:
>
> > > > pull the 'value' data when it's sibling <Name> value = "TO"
>
> > > > I've done the following:
>
> > > > SELECT SubscriptionID, extensionSettings,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
> > > > 'varchar(max)') AS col1,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
> > > > 'varchar(max)') AS col2,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
> > > > 'varchar(max)') AS col3
> > > > from Subscriptions
>
> > > > which gets me the To, CC, & BCC data into output columns 1,2,3. but I
> > > > cannot assume the 2nd value is always of 'CC' type...
>
> > > > I've also tried:
> > > > SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
> > > > ="TO"]/Value)','varchar(max)') AS col1
> > > > from Subscriptions
>
> > > > but this generates an error about " 'value()' requires a
> > > > singleton...."
>
> > > > anyone provide an example of how to accomplish this?
>
> > > > thanks.
>
> wow - it works too!
>
> very nice...i'll have to look at this carefully..
>
> Thanks !
btw..do you know where any documentation on the
extensionSettings.nodes function is?
thanks.
date: Tue, 8 Jul 2008 13:02:31 -0700 (PDT)
author: unknown
Re: newbie to xpath/xquery trying to pull email address out of xml
http://msdn.microsoft.com/en-us/library/ms188282.aspx
--
========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
wrote in message
news:6b33f811-e9ab-47dd-9742-d3baa566ce71@p25g2000hsf.googlegroups.com...
On Jul 7, 7:57 am, kent.eil...@res-direct.com wrote:
> On Jul 3, 5:53 pm, Bob wrote:
>
>
>
> > And shred it. And use a longer VARCHAR ...
>
> > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY KEY,
> > extensionSettings XML )
>
> > DECLARE @xml XML
>
> > INSERT INTO @subscriptions VALUES ( '
> > <ParameterValues>
> > <ParameterValue>
> > <Name>TO</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>BCC</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>ReplyTo</Name>
> > <Value>p...@myemail.com;p...@myemail.com</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>IncludeReport</Name>
> > <Value>True</Value></ParameterValue>
> > <ParameterValue>
> > <Name>RenderFormat</Name>
> > <Value>EXCEL</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Subject</Name>
> > <Value>@ReportName was executed at @ExecutionTime</Value>
> > </ParameterValue>
> > <ParameterValue>
> > <Name>Priority</Name>
> > <Value>NORMAL</Value>
> > </ParameterValue>
> > </ParameterValues>' )
>
> > SELECT e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or
> > Name =
> > "BCC" or Name = "ReplyTo"]') e(a)
>
> > DECLARE @emails TABLE ( email XML )
>
> > INSERT INTO @emails ( email )
> > SELECT '<email>' + REPLACE( y, ';', '</email><email>' ) + '</email>'
> > email
> > FROM
> > (
> > SELECT
> > e.a.value('(Value/text())[1]', 'VARCHAR(50)') y
> > FROM @subscriptions
> > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or
> > Name =
> > "BCC" or Name = "ReplyTo"]') e(a)
> > ) x
>
> > -- And shred it
> > SELECT x.y.value('.', 'VARCHAR(50)')
> > FROM @emails
> > CROSS APPLY email.nodes('email') x(y)
>
> > "Bob" wrote:
> > > Try this:
>
> > > DECLARE @subscriptions TABLE ( SubscriptionID INT IDENTITY PRIMARY
> > > KEY,
> > > extensionSettings XML )
>
> > > DECLARE @xml XML
>
> > > INSERT INTO @subscriptions VALUES ( '
> > > <ParameterValues>
> > > <ParameterValue>
> > > <Name>TO</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>BCC</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>ReplyTo</Name>
> > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>IncludeReport</Name>
> > > <Value>True</Value></ParameterValue>
> > > <ParameterValue>
> > > <Name>RenderFormat</Name>
> > > <Value>EXCEL</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Subject</Name>
> > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > </ParameterValue>
> > > <ParameterValue>
> > > <Name>Priority</Name>
> > > <Value>NORMAL</Value>
> > > </ParameterValue>
> > > </ParameterValues>' )
>
> > > -- pull the 'value' data when it's sibling <Name> value = "TO"
> > > SELECT
> > > --c.d.value('local-name', 'VARCHAR(30)'),
> > > e.a.value('.', 'VARCHAR(30)') x,
> > > -- OR
> > > e.a.value('(text())[1]', 'VARCHAR(30)') y
>
> > > FROM @subscriptions
> > > CROSS APPLY extensionSettings.nodes('//ParameterValue[Name = "TO" or
> > > Name =
> > > "BCC" or Name = "ReplyTo"]/Value') e(a)
>
> > > HTH
> > > wBob
>
> > > "kent.eil...@res-direct.com" wrote:
>
> > > > i'm trying to pull out email addresses info from the SSRS
> > > > subscription
> > > > table. The xml looks like this:
>
> > > > <ParameterValues>
> > > > <ParameterValue>
> > > > <Name>TO</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>BCC</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>ReplyTo</Name>
> > > > <Value>p...@myemail.com;p...@myemail.com</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>IncludeReport</Name>
> > > > <Value>True</Value></ParameterValue>
> > > > <ParameterValue>
> > > > <Name>RenderFormat</Name>
> > > > <Value>EXCEL</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>Subject</Name>
> > > > <Value>@ReportName was executed at @ExecutionTime</Value>
> > > > </ParameterValue>
> > > > <ParameterValue>
> > > > <Name>Priority</Name>
> > > > <Value>NORMAL</Value>
> > > > </ParameterValue></ParameterValues>
> > > > </ParameterValues>
>
> > > > I have not been able to figure out the correct syntax with xpath or
> > > > xquery...my logic is as follows:
>
> > > > pull the 'value' data when it's sibling <Name> value = "TO"
>
> > > > I've done the following:
>
> > > > SELECT SubscriptionID, extensionSettings,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
> > > > 'varchar(max)') AS col1,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
> > > > 'varchar(max)') AS col2,
> > > > CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
> > > > 'varchar(max)') AS col3
> > > > from Subscriptions
>
> > > > which gets me the To, CC, & BCC data into output columns 1,2,3. but
> > > > I
> > > > cannot assume the 2nd value is always of 'CC' type...
>
> > > > I've also tried:
> > > > SELECT CAST(extensionSettings AS XML).value('(//ParameterValue[Name
> > > > ="TO"]/Value)','varchar(max)') AS col1
> > > > from Subscriptions
>
> > > > but this generates an error about " 'value()' requires a
> > > > singleton...."
>
> > > > anyone provide an example of how to accomplish this?
>
> > > > thanks.
>
> wow - it works too!
>
> very nice...i'll have to look at this carefully..
>
> Thanks !
btw..do you know where any documentation on the
extensionSettings.nodes function is?
thanks.
date: Wed, 9 Jul 2008 22:16:49 -0400
author: Michael Coles michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net
|
|
