Showing posts with label element. Show all posts
Showing posts with label element. Show all posts

Tuesday, March 20, 2012

Query Parameter Value Type

What is the attribute/element (such as ValuesTypes, ParameterValuesType..) I should refer to Query Parameter - Value according to the RDL XML Schema Definition as the following example?

<QueryParameters>

<QueryParameter Name="@.CompanyName">

<Value>=Parameters!CompanyName.Value</Value>

</QueryParameter>

</QueryParameters>

Thanks a lot.

Where exactly do you want to refer to the QueryParameter?

I'm not really sure what you are asking. However, if you are wondering where to define the attribute/element of the parameter. You can use this as an example. It is a parameter I created for filtering by Year.

<ReportParameter Name="WHSEDateDimCalendarYear">

<DataType>Integer</DataType>

<Prompt>CalendarYear</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>WHSEDateDimCalendarYear</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaptionIndented</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

|||Thanks for reply to my post. What I am looking for, is the type of Attribute/Element I should refer to, for QueryParamater -Value. In your post, for example, your ValueField (attribute? or element? i'm not sure about it well) is refer to ItemChoiceType31.ValueField, and DatasetName is refer to ItemChoiceType31.DatasetName according to RDL Report Definition XML Schema. I tried to look for the same as for the QueryParameter -Value, cause there are so many Value type defined in the Schema that I did not know what type of Value i should refer to it.

Monday, March 12, 2012

Query out an xml element from a field containing xml data

'message' Field Data:
<xml>
...
<test>ABC</test>
...
</xml
How do I query out 'ABC' from this xml field data in a SQL Server?

Assume message field is in table 'Persons'What you could do, is write a user defined function. Something like

create function [dbo].[fn_GetText]
(
@.Text as XML
)
returns varchar(50)
begin
declare @.Result as varchar(50)
declare @.Count as int
declare @.Start as int

set @.Count = 1
set @.Start = 0

while @.Count <= len(@.Text)
begin
if substring(@.Text, @.Count, 6) = '<test>'
set @.Start = @.Count + 6

if substring(@.Text, @.Count, 7) = '<\test>' and @.Start <> 0
set @.Result = substring(@.Text, @.Start, @.Count - @.Start - 1)

set @.Count = @.Count + 1
end

return @.Result
end

go|||Hi Mike,

thanks for the reply.

As my comfort level is much higher with C#, I just wrote some string
parsing code and got my result :)

Next time I need to spin off a UDF, I will remember this.

Ranjith|||Ranjith (ranjithvenkatesh@.hotmail.com) writes:
> 'message' Field Data:
><xml>
> ...
> <test>ABC</test>
> ...
></xml>
> How do I query out 'ABC' from this xml field data in a SQL Server?
> Assume message field is in table 'Persons'

create table Persons (message xml NOT NULL)
go
insert Persons (message) VALUES('<xml><test>ABC</test></xml>')
go
select message.value(N'(/xml/test)[1]', 'varchar(10)')
from Persons
go
Drop table Persons

This is for SQL 2005. You did not mention which server of SQL Server
you are using. This matters a lot when XML is involved, as the XML
support in SQL 2005 is greatly extended over what is in SQL 2000,
including a new query language, XQuery which I'm using above.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx