How to retrieve data from ntext (xml) data

Question

Post reply

How to retrieve data from ntext (xml) data

JohnDBA

SSC Eights!

Points: 812
More actions
October 28, 2008 at 5:54 pm

#192782

How do I query this field?
Is this possible?
Thank you for your help!
I removed the < for display purpose:
Parameters
Parameter
Name>StartDate/Name>
Type>DateTime/Type>
Nullable>False/Nullable>
AllowBlank>True/AllowBlank>
MultiValue>False/MultiValue>
UsedInQuery>True/UsedInQuery>
State>MissingValidValue/State>
Prompt>Start Date/Prompt>
PromptUser>True/PromptUser>
DynamicDefaultValue>True/DynamicDefaultValue>
/Parameter>
Parameter>
Name>EndDate/Name>
Type>DateTime/Type>
Nullable>False/Nullable>
AllowBlank>True/AllowBlank>
MultiValue>False/MultiValue>
UsedInQuery>True/UsedInQuery>
State>MissingValidValue/State>
Prompt>End Date/Prompt>
PromptUser>True/PromptUser>
Dependencies>
Dependency>StartDate/Dependency>
/Dependencies>
DynamicDefaultValue>True/DynamicDefaultValue>
/Parameter>
Parameter>
Name>Store/Name>
Type>String/Type>
Nullable>False/Nullable>
AllowBlank>False/AllowBlank>
MultiValue>False/MultiValue>
UsedInQuery>True/UsedInQuery>
State>MissingValidValue/State>
Prompt>Plant/Prompt>
PromptUser>True/PromptUser>
DynamicValidValues>True/DynamicValidValues>
DynamicDefaultValue>True/DynamicDefaultValue>
/Parameter>
Parameter>
Name>Customer/Name>
Type>String/Type>
Nullable>False/Nullable>
AllowBlank>False/AllowBlank>
MultiValue>True/MultiValue>
UsedInQuery>True/UsedInQuery>
State>MissingValidValue/State>
Prompt>Source/Prompt>
PromptUser>True/PromptUser>
Dependencies>
Dependency>Store/Dependency>
/Dependencies>
DynamicValidValues>True/DynamicValidValues>
/Parameter>
Parameter>
Name>Colors/Name>
Type>String/Type>
Nullable>True/Nullable>
AllowBlank>False/AllowBlank>
MultiValue>False/MultiValue>
UsedInQuery>True/UsedInQuery>
State>MissingValidValue/State>
Prompt>Colors:/Prompt>
PromptUser>False/PromptUser>
DefaultValues>
Value>Red/Value>
/DefaultValues>Values>
Value>Red/Value>
/Values>
/Parameter>
Parameter>
Name>MyType/Name>
Type>String/Type>
Nullable>False/Nullable>
AllowBlank>False/AllowBlank>
MultiValue>True/MultiValue>
UsedInQuery>True/UsedInQuery>
State>MissingValidValue/State>
Prompt>MyType/Prompt>
PromptUser>True/PromptUser>
Dependencies>
Dependency>Colors/Dependency>
Dependency>Customer/Dependency>
Dependency>Store/Dependency>
/Dependencies>
DynamicValidValues>True/DynamicValidValues>
/Parameter>
Parameter>
Name>CityName/Name>
Type>String/Type>
Nullable>True/Nullable>
AllowBlank>True/AllowBlank>
MultiValue>False/MultiValue>
UsedInQuery>False/UsedInQuery>
State>MissingValidValue/State>
Prompt />
PromptUser>True/PromptUser>
DynamicValidValues>True/DynamicValidValues>
DynamicDefaultValue>True/DynamicDefaultValue>
/Parameter>
/Parameters>"
Final Results will be something like this:
ParameterNameDefaultValues Dependencies
StartDate
EndDate
Store
Customer
Colors Red
MyTypeColors
Customer
Store
CityName

Viewing 13 posts - 1 through 12 (of 12 total)

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

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 1

Are you using this in an application?

If so, I would bring back the whole XML and use XPath Query to return the desired results.

JohnDBA SSC Eights! Points: 812 More actions · Answer 2

JohnDBA

SSC Eights!

Points: 812

October 29, 2008 at 5:36 am

#891124

I am creating a report in reporting services.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 3

Can you attach your XML as a txt document so we can play with it?

JohnDBA SSC Eights! Points: 812 More actions · Answer 4

Ggraber,

Here is the file of one value and one field.

Thank you!

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 5

declare @x xml

set @x='..your xml here..'

select r.value('Prompt[1]','varchar(10)') as ParameterName,

rv.value('.','varchar(10)') as DefaultValues,

rd.value('.','varchar(10)') as Dependencies

from @x.nodes('/Parameters/Parameter') as x(r)

outer apply r.nodes('DefaultValues/Value') as xv(rv)

outer apply r.nodes('Dependencies/Dependency') as xd(rd)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

JohnDBA SSC Eights! Points: 812 More actions · Answer 6

Mark,

So, I cannot select from a field of a table?

I need to select from a file?

I have this table from reporting services and I need to pull data from field Parameter ([Parameter] [ntext] NULL)

CREATE TABLE [dbo].[Catalog](

[ItemID] [uniqueidentifier] NOT NULL,

[Path] [nvarchar](425) NOT NULL,

[Name] [nvarchar](425) NOT NULL,

[ParentID] [uniqueidentifier] NULL,

[Type] [int] NOT NULL,

[Content] [image] NULL,

[Intermediate] [uniqueidentifier] NULL,

[SnapshotDataID] [uniqueidentifier] NULL,

[LinkSourceID] [uniqueidentifier] NULL,

[Property] [ntext] NULL,

[Description] [nvarchar](512) NULL,

[Hidden] [bit] NULL,

[CreatedByID] [uniqueidentifier] NOT NULL,

[CreationDate] [datetime] NOT NULL,

[ModifiedByID] [uniqueidentifier] NOT NULL,

[ModifiedDate] [datetime] NOT NULL,

[MimeType] [nvarchar](260) NULL,

[SnapshotLimit] [int] NULL,

[Parameter] [ntext] NULL,

[PolicyID] [uniqueidentifier] NOT NULL,

[PolicyRoot] [bit] NOT NULL,

[ExecutionFlag] [int] NOT NULL,

[ExecutionTime] [datetime] NULL)

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 7

with cte as (

select cast(Parameter as xml) as Parameter

from [dbo].[Catalog])

select r.value('Prompt[1]','varchar(10)') as ParameterName,

rv.value('.','varchar(10)') as DefaultValues,

rd.value('.','varchar(10)') as Dependencies

from cte

cross apply Parameter.nodes('/Parameters/Parameter') as x(r)

outer apply r.nodes('DefaultValues/Value') as xv(rv)

outer apply r.nodes('Dependencies/Dependency') as xd(rd)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

JohnDBA (10/29/2008)
Ggraber,
Here is the file of one value and one field.
Thank you!

Looks like Mark beat me to it. 🙂

If multiple dependencies or default values existed did you want one row for each of them?

Or did you want only one row for each Parameter and CSV for the values?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

And by the way: this would of course work much faster and much better (many more options) if the Parameter column's data type were XML.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

JohnDBA SSC Eights! Points: 812 More actions · Answer 10

Ggraber,

One row for each of them will be better because there may be several dependencies.

Thank you so much!

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 11

You can get one row per parameter using this

with cte as (

select cast(Parameter as xml) as Parameter

from [dbo].[Catalog])

select r.value('Prompt[1]','varchar(10)') as ParameterName,

(select rv.value('.','varchar(10)') as "data()"

from r.nodes('DefaultValues/Value') as xv(rv) for xml path('')) as DefaultValues,

(select rd.value('.','varchar(10)') as "data()"

from r.nodes('Dependencies/Dependency') as xd(rd) for xml path('')) as Dependencies

from cte

cross apply Parameter.nodes('/Parameters/Parameter') as x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

JohnDBA SSC Eights! Points: 812 More actions · Answer 12

Thank you very much, this worked very nice! 🙂

I do appreciate your help!