XML column to table

Question

XML column to table

philcart

SSC-Forever

Points: 47794
More actions
May 21, 2007 at 2:54 am

#178593

Ok, need a leg up or two here.
What I'm trying to do is get a list of subscriptions and their relevant settings from the SQL Server 2005 ReportServer database.
The field I'm interested in is called ExtensionSettings and is in the Subscriptions table.
This is what the data for one subscription looks like,
<ParameterValues>
<ParameterValue>
<Name>PATH</Name>
<Value>\\srvr01\reports</Value>
</ParameterValue>
<ParameterValue>
<Name>FILENAME</Name>
<Value>Client SLA report</Value>
</ParameterValue>
<ParameterValue>
<Name>FILEEXTN</Name>
<Value>True</Value>
</ParameterValue>
<ParameterValue>
<Name>USERNAME</Name>
<Value>xyz</Value>
</ParameterValue>
<ParameterValue>
<Name>PASSWORD</Name>
<Value>123</Value>
</ParameterValue>
<ParameterValue>
<Name>RENDER_FORMAT</Name>
<Value>PDF</Value>
</ParameterValue>
<ParameterValue>
<Name>WRITEMODE</Name>
<Value>Overwrite</Value>
</ParameterValue>
</ParameterValues>
What I'd like is to output the data something like the following, or even a single comma-seperated column would be ok,
NameValue
PATH\\srvr01\reports
FILENAMEClient SLA report
FILEEXTNTrue
USERNAMExyz
PASSWORD123
RENDER_FORMATPDF
WRITEMODEOverwrite
Here is my code so far. I've been beating my head against the wall with this XML stuff and haven't really got anywhere. If you know a better way to achieve the desired result, I'm all ears.
drop function dbo.udf_XML2Table
go
CREATE FUNCTION dbo.udf_XML2Table (@pk uniqueidentifier, @xCol xml)
RETURNS table WITH SCHEMABINDING
AS RETURN(
select @pk as SubscriptionID
, nref.value('.', 'varchar(max)') as ParamName
, null as ParamValue
from @xCol.nodes('/ParameterValues/ParameterValue/Name') R(nref)
union all
select @pk as SubscriptionID
, null ParamName
, nref.value('.', 'varchar(max)') as ParamValue
from @xCol.nodes('/ParameterValues/ParameterValue/Value') R(nref)
)
go
select
p.SubscriptionID
, x.ParamName
, x.ParamValue
from (
select '3C1CC6E9-71D5-4BC5-B9FA-71BAD3776F4B' as SubscriptionID
, cast('<ParameterValues>
<ParameterValue>
<Name>PATH</Name>
<Value>\\srvr01\reports</Value>
</ParameterValue>
<ParameterValue>
<Name>FILENAME</Name>
<Value>Client SLA report</Value>
</ParameterValue>
<ParameterValue>
<Name>FILEEXTN</Name>
<Value>True</Value>
</ParameterValue>
<ParameterValue>
<Name>USERNAME</Name>
<Value>xyz</Value>
</ParameterValue>
<ParameterValue>
<Name>PASSWORD</Name>
<Value>123</Value>
</ParameterValue>
<ParameterValue>
<Name>RENDER_FORMAT</Name>
<Value>PDF</Value>
</ParameterValue>
<ParameterValue>
<Name>WRITEMODE</Name>
<Value>Overwrite</Value>
</ParameterValue>
</ParameterValues>
' as xml) as ExtensionSettings
from ReportServer.dbo.subscriptions
) as p
cross apply ssvnts.dbo.udf_XML2Table(p.SubscriptionID, p.ExtensionSettings) as x
go
--------------------
Colt 45 - the original point and click interface

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Also, just to throw oil on the fire, the number of Parameter values depends on what delivery method and other options are selected.

--------------------
Colt 45 - the original point and click interface

jacob sebastian SSChampion Points: 11822 More actions · Answer 2

The following code generates a resultset with two columns. (name and value).

declare

@x xml

set @x = '<ParameterValues>

<Value>\\srvr01\reports</Value>

</ParameterValue>

<Name>FILENAME</Name>

<Value>Client SLA report</Value>

</ParameterValue>

<Name>FILEEXTN</Name>

</ParameterValue>

<Name>USERNAME</Name>

</ParameterValue>

<Name>PASSWORD</Name>

</ParameterValue>

<Name>RENDER_FORMAT</Name>

</ParameterValue>

<Name>WRITEMODE</Name>

<Value>Overwrite</Value>

</ParameterValue>

</ParameterValues>'

SELECT

x

.param.value('Name[1]', 'varchar(100)') as name,

x

.param.value('Value[1]', 'varchar(100)') as value

from

@x

.nodes('//ParameterValues/ParameterValue') x(param)

output:

name value

-------------------- --------------------

PATH \\srvr01\reports

FILENAME Client SLA report

FILEEXTN True

USERNAME xyz

PASSWORD 123

RENDER_FORMAT PDF

WRITEMODE Overwrite

does this help?

.

philcart SSC-Forever Points: 47794 More actions · Answer 3

Jacob, thanks for that. How do I run this across a table?

The subscription table in the Reporting Services database has three fields with XML values that I'd like to break out into columns.

--------------------
Colt 45 - the original point and click interface

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

This sample works on the "subscription" table. It takes the first row and breaks the parameters into two columns.

"parameters" is NTEXT so I cast it to XML with:

SELECT TOP 1 CAST(parameters AS XML) AS param FROM subscriptions

Now, this subtable is used in the following query.

SELECT

x

.param.value('Name[1]', 'varchar(100)') as Name,

x

.param.value('Field[1]', 'varchar(100)') as field

FROM

(

SELECT TOP 1 CAST(parameters AS XML) AS param FROM subscriptions) as P

CROSS

APPLY param.nodes('//ParameterValues/ParameterValue') x(param)

.

philcart SSC-Forever Points: 47794 More actions · Answer 5

Ok, that's working for the Parameters and ExtensionSettings columns. Now I've struck an iceberg while the scheduling information. The column is called MatchData in the subscription table.

Here is a sample of the xml,

</DaysOfWeek>

</WeeklyRecurrence>

</ScheduleDefinition>

I might try and see if I can get the scheduling info from one of the other tables.

--------------------
Colt 45 - the original point and click interface

jacob sebastian SSChampion Points: 11822 More actions · Answer 6

jacob sebastian

SSChampion

Points: 11822

June 8, 2007 at 12:33 am

#711593

do you still have a problem?

.

philcart SSC-Forever Points: 47794 More actions · Answer 7

Haven't had a chance to look through the other schedule tables at the moment. If you've got any ideas on how to use the XML data as it is that'll be a start.

I don't think it'll be possible though because the structure changes depending on the type of schedule that is created. The sample I posted is for a weekly recurring schedule that runs on Monday.

--------------------
Colt 45 - the original point and click interface

jacob sebastian SSChampion Points: 11822 More actions · Answer 8

Phil,

What exactly is the problem?

do you have a problem to read values from the XML column? if yes, i can help you out.

thankx

Jacob

.

philcart SSC-Forever Points: 47794 More actions · Answer 9

Well given the structure posted

- How do I get the "True" value for Monday?

- How would I get the same value if the node was Tuesday and the Monday node didn't exist?

- How would I extract the values if the schedule was a Monthly recurrence on the 1st of the month? In this case the WeeklyRecurrence node would not exist.

I have a feeling that I won't be able to apply your great query against this bit of XML because the structure is so dynamic.

I'm going to have a hunt through the schedule table this weekend. If I come up with anything you'll be the first to know

--------------------
Colt 45 - the original point and click interface