How to filter with hierarchy

Hi,

I have a cube which has a "Relationship Hierarchy" dimension which is hierarchical. I have written the following MDX query which has a filter on the rows axis...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
FILTER(
Hierarchize({[Relationship Hierarchy].[Level1 Full Name].members}),
[Relationship Hierarchy].currentmember is [AXA SA]]
)
}
ON ROWS
FROM [MyMart] WHERE ([Measures].[Costs])

The query works as expected, but now I want to do something a little more advanced and I can't figure out how. I would like to have all children of 'AXA SA' returned too. So basically, I want to say this...

FILTER(<set>, [Relationship Hierarchy].currentmember is [AXA SA] or [Relationship Hierarchy].currentmember is [AXA SA].children)

Obviously the "is [AXA SA].children" syntax is not valid. But does anyone know how to do this?

Similarly, is possible to say I want any members from "[Relationship Hierarchy]" who have a parent of "[AXA SA]"?

Thanks,

Lachlan

In your example, the Filter function is redundant. You can simply use { [AXA SA] } or [AXA SA].children to get the sets you want.|||

Hi,

Sorry, maybe my question didn't really make sense. What I wanted to do was only filter on the level 1 members in my [Relationship Hierarchy] dimension. The problem with my query above is that the children are never retrieved because the filter is also applied to them too. I just needed to move the filter to the correct place so the children are not filtered too...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
Hierarchize({ FILTER([Relationship Hierarchy].[Level1 Full Name].members, [Relationship Hierarchy].currentmember is [AXA SA]), AddCalculatedMembers( {[Relationship Hierarchy].[All Relationship Hierarchy].[AXA SA].children} )})
}
ON ROWS
FROM [mymart]
WHERE ([Measures].[Costs])

How to filter with hierarchy

Hi,

I have a cube which has a "Relationship Hierarchy" dimension which is hierarchical. I have written the following MDX query which has a filter on the rows axis...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
FILTER(
Hierarchize({[Relationship Hierarchy].[Level1 Full Name].members}),
[Relationship Hierarchy].currentmember is [AXA SA]]
)
}
ON ROWS
FROM [MyMart] WHERE ([Measures].[Costs])

The query works as expected, but now I want to do something a little more advanced and I can't figure out how. I would like to have all children of 'AXA SA' returned too. So basically, I want to say this...

FILTER(<set>, [Relationship Hierarchy].currentmember is [AXA SA] or [Relationship Hierarchy].currentmember is [AXA SA].children)

Obviously the "is [AXA SA].children" syntax is not valid. But does anyone know how to do this?

Similarly, is possible to say I want any members from "[Relationship Hierarchy]" who have a parent of "[AXA SA]"?

Thanks,

Lachlan

In your example, the Filter function is redundant. You can simply use { [AXA SA] } or [AXA SA].children to get the sets you want.|||

Hi,

Sorry, maybe my question didn't really make sense. What I wanted to do was only filter on the level 1 members in my [Relationship Hierarchy] dimension. The problem with my query above is that the children are never retrieved because the filter is also applied to them too. I just needed to move the filter to the correct place so the children are not filtered too...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
Hierarchize({ FILTER([Relationship Hierarchy].[Level1 Full Name].members, [Relationship Hierarchy].currentmember is [AXA SA]), AddCalculatedMembers( {[Relationship Hierarchy].[All Relationship Hierarchy].[AXA SA].children} )})
}
ON ROWS
FROM [mymart]
WHERE ([Measures].[Costs])

How to filter with a type-in parameter in SSRS using SSAS cube data?

I'm using SSRS and SSAS 2005. I know how to create a type-in parameter in SSRS. However, I'm not sure how to filter the SSAS cube data using the value from the type-in parameter. At the end of the day, my user'd like to see the SSRS report with just the filtered value. Any help would be great.

Mitch

I have found out how to do it by following this link http://msdn2.microsoft.com/en-us/library/aa337223.aspx.

Mitch

How to filter this?

I Have 2 tables

Tables

Cat. ID

Category 1

2

Cat. Id Brand ID

Brand 1 129007

2 129999

And i had try to write the MDX as follow

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

But result is displayed all the result, How should i filter to only display Cat.ID =1 ?

1 2 3 4

VOP PPU CDC FOP

129007 90.8 99.9 78.9 77.3

129999 3.3 2.2 1.1 2.2 >This Rows should be filtered..

Thanks In advance

Hi tsohtan,

just add a WHERE-clause like the following:

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1])

Michael.

|||

Hi Michael,

Thanks for you answer.

How about filter more than one condition?

is that like this?

where condition1 & condition2?

Because i not sure is my data problem, it come out different result it should generate.

Thanks again.

|||

Hi tsohtan,

it depends on wether you want to filter by the same dimension but several members or by a member of another dimension.

Case 1: Filtering by more than one dimension

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE {[Dim Category].[Dim Category].&[1], [Dim Category].[Dim Category].&[2]}

Case 2: Filtering by a member of another dimension

Code Snippet

[Dim Category].[Dim Category].&[1]

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1], [Dim Other Dimension].[Dim Other Dimension].&[42])

You can also combine this. You can read in BOL about the MDX basics in the MDX Reference chapter. The basic elements of MDX (member, tuple and set) are explained there.

Michael.

|||

Hi Michael,

I not sure what is the "Where" mean here, a bit confuse.

Because i set the WHERE (Dim Category].[Dim Category].&1) and without the "WHERE" condition.

The total counts is same.

Any idea?

Thanks again

|||

Hi tsohtan,

maybe your Category dimension is not properly joined with your measure group?

Michael.

|||

yes you are right. and i get the right result.

thank you very much michael

How to filter this?

I Have 2 tables

Tables

Cat. ID

Category 1

2

Cat. Id Brand ID

Brand 1 129007

2 129999

And i had try to write the MDX as follow

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

But result is displayed all the result, How should i filter to only display Cat.ID =1 ?

1 2 3 4

VOP PPU CDC FOP

129007 90.8 99.9 78.9 77.3

129999 3.3 2.2 1.1 2.2 >This Rows should be filtered..

Thanks In advance

Hi tsohtan,

just add a WHERE-clause like the following:

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1])

Michael.

|||

Hi Michael,

Thanks for you answer.

How about filter more than one condition?

is that like this?

where condition1 & condition2?

Because i not sure is my data problem, it come out different result it should generate.

Thanks again.

|||

Hi tsohtan,

it depends on wether you want to filter by the same dimension but several members or by a member of another dimension.

Case 1: Filtering by more than one dimension

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE {[Dim Category].[Dim Category].&[1], [Dim Category].[Dim Category].&[2]}

Case 2: Filtering by a member of another dimension

Code Snippet

[Dim Category].[Dim Category].&[1]

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1], [Dim Other Dimension].[Dim Other Dimension].&[42])

You can also combine this. You can read in BOL about the MDX basics in the MDX Reference chapter. The basic elements of MDX (member, tuple and set) are explained there.

Michael.

|||

Hi Michael,

I not sure what is the "Where" mean here, a bit confuse.

Because i set the WHERE (Dim Category].[Dim Category].&1) and without the "WHERE" condition.

The total counts is same.

Any idea?

Thanks again

|||

Hi tsohtan,

maybe your Category dimension is not properly joined with your measure group?

Michael.

|||

yes you are right. and i get the right result.

thank you very much michael

How to filter the row in merge replication.

Hi There,
I would like to filter the row in several tables in merge replication
for the subscribers. I would like to filter the row with the criteria
saying only trasfer the data for last week or latest last(7 days data)
to the subcriber and the second option is first 100 rows. If there is
any information in this please forward to me.
Thanks a lot.
Indra.
Indra,
there is a parameter on the merge agent that you might like to look
at: -MaxDownloadChanges.
However, I'd like to know a little more about your scenario. On the
publisher no doubt there is some data older than a week. Is this data
intended to exist on the subscriber? What about if some of this older data
is modified on the publisher, should the change be propagated? Please post
up a bit more info.
TIA,
PAul Ibison
|||You can use static row filtering, which is documented in Books Online.
thanks
gopal
|||Hi Paul,
I reply for this message long time back but its not apper here, replying
again.
Acutally we have one server working as publisher, distributer and
several subscriber (install MSDE, sql client utility) and having adp
front end for the applicaiton. since they donot need all the data, i
just wanted to send them for few table data of say last two week or 20
etc. actuall publisher may have data which have been modified in the
main office, which in not necessary to send it back to them, becasue we
are receiving all the job information form the field office and once we
received it we will process othe information here in main office and it
not necessary to replicate or propagated back to the subscriber.
As mention by Gopal, I try to check with static filter its giving me
porblem. I willtry again tomorrow and let you know how its work.
Thanks for help.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||One thing to be very careful with is what you want the subscriber to have.
When you implement a filter, merge will literally enforce that filter across
your data. That means if your filter is for say the last 20 days and
someone inserts a row of data older than this, that piece of data will be
removed from the subscriber after it is sent to the publisher.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

How to filter sqldatasource

Hi,

I am new in framework 2 and I can't find a way to filter the sqldatasource.

I have an sqldatasource control that retrive data from data base-"Select * from myTable"

I set the fiterExpression property-sqlDataSource1.FilterExpression="ID='" + strID + "' " ;

I don't know how to continue from here.If I bound the sqlDataSource1 to a control like gridView it works good and I see the filter oparation. but I want to get the result set in the code and loop threw it like I did with ver 1.1 with sqldataReader:

While sqlDatareader1.Read { myCode ... }

How can I do it with sqlDataSource ?

Thanks,

David

I copied my answer for this question:

If your DataSourceMode of your SQLDataSource stays as default which is DataSet, you can retrieve a dataview object of your SQLDataSource. If DataSourceMode="DataReader" in yourSQLDataSource, you can retrieve a datareader object.

Here is the an sample for dataview:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

'DataView
Dim mydataview As System.Data.DataView =CType(SQLDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

For Each dr As DataRow In dv.Table.Rows
...

Next

'For a Datareader

Dim myreader as System.Data.SqlClient.SqlDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.SqlClient.SqlDataReader)

While myreader.reader

...

End While

You can use parameterized query for your datasource's sql selection statement.

For example:(DDL1 ia a dropdownlist)

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Personal %>"
SelectCommand="SELECT [mycol1], [mycol2], [mycol3], [myDate], [firstName] FROM [Table1] WHERE ([firstName=@.firstName)">
<SelectParameters>
<asp:ControlParameter ControlID="DDL1" Name="firstName" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>

HTH.

|||hi david,
I think a much simpler way to handle code-behind data operations would be to use the current version of the Data Access Application Block in the Enterprise Library, available at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib2.asp

IMHO, it's much simpler for the kind of while datareader.read and the stuff we used to do in 1.1. The download comes with quite comprehensive documentation, give it a try.

fendi,|||

Hi Limno,

Thanks for your helpful answer. Now I understand sqlDataSource better.

Have a good week.

David.

|||

Hi fendi,

Thanks for your advice.I'll keep it .

Have a good week.

David

How to filter results for ''All Periods''?

Hi, all,

How could we filter the results to exclude 'All Periods'? E.g. I would like to see sales amount for year of 2001,2002. etc, and I dont want to see the results retured for 'All Periods', thus how could we achieve this?

Thanks.

With best regards,

Yours sincerely,

Hi, experts,

Anyone of you know the answer? Thanks and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

|||

Hello Helen! I am not sure about your problem but this is what you get if you put years on rows or columns in Proclarity Professional. You do not have to filter on years in order to se the years that you would like.

Simply put the years you would like on rows or columns.

If you slice on years,that is choose one or several members in a dimension as a background dimension i Proclarity,

you can pick the year that you are interested in.

Can you tell more about what you are interested in?

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for that.

What I was wondering is to filter the 'All Periods' in the results returned by SSMS (Management Studio) against the MDX query launched there.

Hope it is clear for your help and advices.

With best regards,

Yours sincerely,

|||

Hello Helen! If you run this MDX on the Adventure Works cube:

Code Snippet

Select {[Date].[Calendar].[Calendar Year].Members} On Columns,

[Product].[Product Categories].[Category].Members On Rows

From [Adventure Works]

Where ([Measures].[Internet Sales Amount]);

Is this what you are looking for?

Kind Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your help. Yes, that's what I am looking for. And how can we add subtotals for each column and each row to the results returned?

Thanks and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

|||

Hello Helen. In ProClarity Professional 6.3 you have an MDX-editor. I think it is under the meny-view.

Create subtotals in ProClarity and copy & paste that code to Management Studio.

That is a good start.

Regards

Thomas Ivarsson

|||

Hi,Thomas,

Thanks a lot and obviously this is an effective way of doing it.

With best regards,

Yours sincerely,

How to filter reports user logon info

I am new to reporting services
Is there an example of how to obtain user logon information to use as input
for queries before report renders. We will deploy reports to a portal and
want to use the user logon info as a filter for initial queries ( for example
to restrict information viewed by client ). Thanks.You can use the User!UserID global, which is the logged on username. Pass
it into a query parameter. Of course, you will need to have data in your
source database tables that includes the UserID values, to join and filter
your data.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
>I am new to reporting services
> Is there an example of how to obtain user logon information to use as
> input
> for queries before report renders. We will deploy reports to a portal and
> want to use the user logon info as a filter for initial queries ( for
> example
> to restrict information viewed by client ). Thanks.|||Where is it picking up the global value from? When I was working with
Cleverpath portal before (CA's portal product) the reports were published to
the portal and the logon id was obtained from the portal signon. In the case
of RS, the reports are deployed to the report server - are there tables
associated with the report server? Is it getting the value from the browser
session?
"Jeff A. Stucker" wrote:
> You can use the User!UserID global, which is the logged on username. Pass
> it into a query parameter. Of course, you will need to have data in your
> source database tables that includes the UserID values, to join and filter
> your data.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
> >I am new to reporting services
> > Is there an example of how to obtain user logon information to use as
> > input
> > for queries before report renders. We will deploy reports to a portal and
> > want to use the user logon info as a filter for initial queries ( for
> > example
> > to restrict information viewed by client ). Thanks.
>
>|||Yes, it's getting the user name from the session. If you have Windows
Authentication, it's getting it from the OS. If you have custom security
and forms auth, then it's getting it from the forms auth cookie.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:A5CD5045-9C76-4F0E-B092-F1AB2B372A1E@.microsoft.com...
> Where is it picking up the global value from? When I was working with
> Cleverpath portal before (CA's portal product) the reports were published
> to
> the portal and the logon id was obtained from the portal signon. In the
> case
> of RS, the reports are deployed to the report server - are there tables
> associated with the report server? Is it getting the value from the
> browser
> session?
> "Jeff A. Stucker" wrote:
>> You can use the User!UserID global, which is the logged on username.
>> Pass
>> it into a query parameter. Of course, you will need to have data in your
>> source database tables that includes the UserID values, to join and
>> filter
>> your data.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
>> >I am new to reporting services
>> > Is there an example of how to obtain user logon information to use as
>> > input
>> > for queries before report renders. We will deploy reports to a portal
>> > and
>> > want to use the user logon info as a filter for initial queries ( for
>> > example
>> > to restrict information viewed by client ). Thanks.
>>

How to filter Profiler trace by dbid in 2005?

Is there any way to filter a SQL Profiler trace by dbid? This was a feature of earlier versions, but I can't figure it out in 2005.

Thanks!

Hi there.

From the Profiler GUI, create a new trace, move to the Events Selection tab, click the Show all Columns check box (this is important, as the filters box will only show columns that are selected in the interface), then click the Column Filters button in the lower right corner of the box, you should see the DatabaseID option about 1/3 of the way down the list box on the left...click it, then set the appropriate value in the text box in the lower right of the screen...

HTH,

|||

Yep... it's a little tricky because it's not shown by default.

Steps:

1) Create a new trace.
2) Click the Events Selection tab.
3) Check the box Show All Columns.
4) Click the Column Filters... button.
5) Choose DatabaseID from the listbox on the left.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Aha! This is one of those 2 step processes. Thanks a lot!

how to filter output based on user's AD group membership?

I want to filter reports based on a users active directory security i.e. what
parameters they can select, what columns they can see
But I can't figure out the best way of doing this and the documentation on
AD seems to assume that the user already understands AD.
From what I can tell I have two options
1) use sql with the openquery syntax - don't think this is going to be an
option due to the hassle here of setting up linked servers (bureaucracy in
the extreme)
2)use system.directoryservices with some code built into to the report - i
don't really understand how to do this, none of the code samples i have seen
seem to do what I want e.g. pass in username from global report parameters
along with the groupname i want to check against, and return whether they are
in that particular group as true/false
help greatly appreciated!
do you know a better way of doing this? code samples? etc
thanks!AD queries in SQL Server using the ADSI can be a problem... I am told it will
only search the first 1000 rows returned by the AD..
if you are using SQL 2005... Take a look at the sys.login_token
It shows all of the AD groups the user is a member of.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"adolf garlic" wrote:
> I want to filter reports based on a users active directory security i.e. what
> parameters they can select, what columns they can see
> But I can't figure out the best way of doing this and the documentation on
> AD seems to assume that the user already understands AD.
> From what I can tell I have two options
> 1) use sql with the openquery syntax - don't think this is going to be an
> option due to the hassle here of setting up linked servers (bureaucracy in
> the extreme)
> 2)use system.directoryservices with some code built into to the report - i
> don't really understand how to do this, none of the code samples i have seen
> seem to do what I want e.g. pass in username from global report parameters
> along with the groupname i want to check against, and return whether they are
> in that particular group as true/false
> help greatly appreciated!
> do you know a better way of doing this? code samples? etc
> thanks!

How to filter out unwanted nodes?

I would like to retrieve the values of ProductIDs nodes but the condition is to ignore any ProductIDs that is a child of <AlternativeState> node.

Please keep in mind that xml data comes from table and the value of ProductIDs could appear anywhere else as well so I am afraid using exact XPath is not a choice. The only time when ProductIDs is not returned when its a child of AlternativeState.

Currently I have something like this but it returns all of the productIDs from the xml instance

SELECT UsageID, xmlState.query('

for $productIDs in //*/ProductIDs

order by $productIDs

return string($productIDs)') AS ProductIDs

FROM Usage.UsageAnalysis

WHERE UsageID = 91923

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<ProductIDs>46420</ProductIDs>

<AlternativeState><!-- Ignore anything in this element-->

<OtherEvents>

<ProductIDs>46420</ProductIDs>

</OtherEvents>

<MarketSize>

<ProductIDs>46431,46440,46469,46470</ProductIDs>

</MarketSize>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<ProductIDs>2128,2459,46420,46440,46470</ProductIDs>

</MarketSize>

</CurrentState>

</StatsState>

</State>

In your sample XML the AlternativeState element does not have a ProductIDs child, only a grand child. So for that XML sample the expression

Code Snippet

for $productIDs in //*[not(self::AlternativeState)]/*/ProductIDs

order by $productIDs

return string($productIDs)

should filter out what you want to filter out. Using an ancestor axis test should solve the problem in general but that axis is not currently supported in XQuery for SQL Server 2005.

How to filter out unwanted data

I have the following fields in table A:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 41142 | 1,800.00
20/02/2006 | 41142 | 2,700.00
25/02/2006 | 740245 | 5,200.00
I have the following fields in table B:
Date | Descrip | Amt Dr
--
02/02/2006 |88258 | 1,400.00
17/02/2006 |740244 | (1,500.00)
25/02/2006 |740245 | 5,200.00
There are no referencial key between TableA & TableB,
What i want is to extract the date,descrip & Amt data from
Table A where it's Descrip data is not the same as the data
in Table B's Descrip column.
My sql syntax is as follows:
SELECT
CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Amt - (TableA.Amt * 2)ELSE 0 END AS
[Add Back]
FROM TableA,TableB
WHERE TableA.Descrip <> TableB.Descrip
GROUP BY TableA.Date,TableA.Amt,TableA.Descrip
The output of the above is as follows:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 0 | 0
20/02/2006 | 0 | 0
25/02/2006 | 740245 | 5,200.00
Notice that, descrip with 740245 appearing in both tables
is what the sql should filter out, but failed to do so,
what i want is as below:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
Can i achieve this? Please help.
*** Sent via Developersdex http://www.examnotes.net ***Dave
Untested
SELECT * FROM TableA WHERE NOT EXISTS
(SELECT * FROM TableB WHERE TableA.Descr<>Table.Descr)
"Dave dcartford" <dcartford@.gmail.com> wrote in message
news:ucygLFEaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>
> I have the following fields in table A:
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
> 15/02/2006 | 41142 | 1,800.00
> 20/02/2006 | 41142 | 2,700.00
> 25/02/2006 | 740245 | 5,200.00
> I have the following fields in table B:
> Date | Descrip | Amt Dr
> --
> 02/02/2006 |88258 | 1,400.00
> 17/02/2006 |740244 | (1,500.00)
> 25/02/2006 |740245 | 5,200.00
> There are no referencial key between TableA & TableB,
> What i want is to extract the date,descrip & Amt data from
> Table A where it's Descrip data is not the same as the data
> in Table B's Descrip column.
> My sql syntax is as follows:
>
> SELECT
> CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END,
> CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END,
> CASE WHEN TableA.Amt < 0 THEN TableA.Amt - (TableA.Amt * 2)ELSE 0 END AS
> [Add Back]
> FROM TableA,TableB
> WHERE TableA.Descrip <> TableB.Descrip
> GROUP BY TableA.Date,TableA.Amt,TableA.Descrip
> The output of the above is as follows:
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
> 15/02/2006 | 0 | 0
> 20/02/2006 | 0 | 0
> 25/02/2006 | 740245 | 5,200.00
> Notice that, descrip with 740245 appearing in both tables
> is what the sql should filter out, but failed to do so,
> what i want is as below:
>
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
>
> Can i achieve this? Please help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Is someone else posting from Uri's name.. juz joking :)
try this
SELECT * FROM TableA WHERE NOT EXISTS
(SELECT * FROM TableB WHERE TableA.Descr=Table.Descr)
"Uri Dimant" wrote:

> Dave
> Untested
> SELECT * FROM TableA WHERE NOT EXISTS
> (SELECT * FROM TableB WHERE TableA.Descr<>Table.Descr)
>
> "Dave dcartford" <dcartford@.gmail.com> wrote in message
> news:ucygLFEaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>
>|||>> > Table A where it's Descrip data is not the same as the data
Just change NOT EXISTS to EXISTS
create table #table_a (c1 datetime,descr varchar(10))
create table #table_b (c1 datetime,descr varchar(10))
insert #table_a values ('20060101','a')
insert #table_a values ('20060101','h')
insert #table_a values ('20060101','r')
insert #table_b values ('20060101','a')
insert #table_b values ('20060101','c')
select * from #table_a where exists
(select * from #table_b where #table_a.descr=#table_b.descr)
drop table #table_a,#table_b
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:3F740FCC-A686-49F9-BF5C-7544C822E32D@.microsoft.com...
> Is someone else posting from Uri's name.. juz joking :)
> try this
> SELECT * FROM TableA WHERE NOT EXISTS
> (SELECT * FROM TableB WHERE TableA.Descr=Table.Descr)
> "Uri Dimant" wrote:
>|||Uri,
Shouldn't the query be like this' I apologise if I irritated you.
select * from #table_a where not exists
(select * from #table_b where #table_a.descr=#table_b.descr)|||Hi
Doh, I need a cofee , my mistake. I don't why ,i have been assumed that he
needs the same descr to get out
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6A886FB3-F225-4863-A453-C0E6C07F1819@.microsoft.com...
> Uri,
> Shouldn't the query be like this' I apologise if I irritated you.
> select * from #table_a where not exists
> (select * from #table_b where #table_a.descr=#table_b.descr)
>

How to filter out the items between the delimiter ";"?

Hello,

Inside a column I have this result:
Record 1: Sales;Admins
Record 2: ;Sales;Admins
Record 3: Sales;
Record 4: Admins;

You can see the delimiter ";", it can be everywhere.

Now I want to delete "Sales".
Therefor I have to search where the "Sales" is. (records)
After that I want to delete the "Sales".

If I delete it the record may not have 2 or more delimiters after each other, like here:
Record 1: ;Admins (good, better is to remove the delimiter also)
Record 2: ;;Admins (bad)
Record 3: ;(good, better is to remove the delimiter also)
Record 4: Admins;

Can somebody help me how to build this query?

Thanks!check for more info on REPLACE function ...
This example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx')
GO

hth|||Sounds like someones breaking their database rules, tut tut ;)|||First normal... second normal... I know but this is simpler then make a lot of database hits each time.|||How smart is the replace function?

Let's say that I have this:
sales;adminsales;admins

I want to replace sales, this means that it must look like this:
adminsales;admins

It may not change adminsales, this is wrong:
admin;admins

Is the replace function a good option for me?

(Is there not something like a split function?)

Thanks|||If you MUST do this, then you'd be much better off always enclosing the data with delim's. E.g. ;sales;;adminsales;;admins;

That way you can use the replace without having the worries you've mentioned for the sake of a few bytes.|||Then I have to search for ";sales;" in this example?

replace(mycolumn,';sales;','')

|||yes, but providing you don't allow ";" then you won't suffer from those Admin or AdminSales problems. I hate all the edge cases you have to worry about with CSVs.

How to filter our system objects from the list

I'm using following statement to get the list of objects in given SQL
Server database (this is taken from VB6 code):
lstrSQL = "select name, refdate from dbo.sysobjects where " &
lstrFilter & " order by name"
I apply filter to narrow the results to particular type, for example
for procedures:
lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
For functions:
lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
N'IsTableFunction') = 1"
etc.
My question is: how can I filter out the objects created by the system
(I want only the objects created by the users to be left)?
Ideally, I'd like the solution to work in SQL Server 2005 and in the
older versions.
TIA
Dariusz Dziewialtowski.Use the xtype column to exclude the objects you don't want to show:
xtype: Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||select *
from sysobjects
where xtype <> 'S'
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Use the xtype column to exclude the objects you don't want to show:
> xtype: Object type. Can be one of these object types:
> C = CHECK constraint
> D = Default or DEFAULT constraint
> F = FOREIGN KEY constraint
> L = Log
> FN = Scalar function
> IF = Inlined table-function
> P = Stored procedure
> PK = PRIMARY KEY constraint (type is K)
> RF = Replication filter stored procedure
> S = System table
> TF = Table function
> TR = Trigger
> U = User table
> UQ = UNIQUE constraint (type is K)
> V = View
> X = Extended stored procedure
>
> "dariusz.dziewialtowski@.gmail.com" wrote:
>|||Try,
...
and objectproperty([id], 'IsMSShipped') = 0
AMB
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||Use the IsMSShipped object property|||Edgardo, Alejandro, Scott
Thank you for your so quick responses!
I was thinking about using IsMSShipped in the past but I was afraid
that it wouldn't work right - for example: the whole Northwind database
is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
all objects in that database in that case?
Hmm, I have to test it actually...
Again - thanks a lot for your help!
Dariusz Dziewialtowski.|||That's a good point.
Actually, the IsMSShipped property is only set for those objects that
someone set it on for. Anyone can mark an object so that the IsMSShipped bit
is true by running sp_MS_marksystemobject.
So there is really no way to know for sure what is shipped by MS and what
isn't. You'll have to come up with another way of determining which objects
you want to see and which you don't.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144681248.829348.140770@.i40g2000cwc.googlegroups.com...
> Edgardo, Alejandro, Scott
> Thank you for your so quick responses!
> I was thinking about using IsMSShipped in the past but I was afraid
> that it wouldn't work right - for example: the whole Northwind database
> is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
> all objects in that database in that case?
> Hmm, I have to test it actually...
> Again - thanks a lot for your help!
>
> Dariusz Dziewialtowski.
>|||Kalen,
Thanks a lot for your explanation!
Dariusz Dziewialtowski

How to Filter only stored procedure when executing sp_depends ?

Hi !
I'd like to filter only the stored procedure when executing sp_depends on a
table.
How can I do that easily ?
Thx for your help.
JeffCreate a temporary table to store the sp result.
Example:
use northwind
go
create table #t (
[name] sysname,
type varchar(50)
)
insert into #t
exec sp_depends orders
select
*
from
#t
where
type = 'stored procedure'
drop table #t
go
AMB
"Jeff37" wrote:

> Hi !
> I'd like to filter only the stored procedure when executing sp_depends on
a
> table.
> How can I do that easily ?
> Thx for your help.
> Jeff

how to filter IPs that can connect to 1433 ?

Hi,
I have SQL 2005 express running on 2003 server (standard). How do I filter
packets to allow on 2 IP addresses to connect to 1433 ?
My router has a NAT table for port forwarding but I don't have an firewall
rules to filter IP traffic.
Is these a facility on 2003 server or SQL 2005 that would allow me to packet
filter by IP address ?
(guess I could just install ISA server on it but this seems a little
extreme).
Thanks
Scottits ok found the ip security in local policy.

how to filter IPs that can connect to 1433 ?

Hi,
I have SQL 2005 express running on 2003 server (standard). How do I filter
packets to allow on 2 IP addresses to connect to 1433 ?
My router has a NAT table for port forwarding but I don't have an firewall
rules to filter IP traffic.
Is these a facility on 2003 server or SQL 2005 that would allow me to packet
filter by IP address ?
(guess I could just install ISA server on it but this seems a little
extreme).
Thanks
Scottits ok found the ip security in local policy.

How to Filter Expression in Data Region

how do i filter any expression in the data region
e.g. i want all users for just New York?
how to apply that filter condition in the expression in data region?See BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_3diq.asp
Example:
Filter expression: =Fields!City.Value
Operator: =Value expression: ="New York" or just New York (because it is a string)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:D5F8D505-BC13-4F20-887A-62BC1BF2A669@.microsoft.com...
> how do i filter any expression in the data region
> e.g. i want all users for just New York?
> how to apply that filter condition in the expression in data region?|||Sorry, but based on your questions I thought you want to filter data.
However, you actually want to calculate some aggregates. Try this
expression:
=Count( iif(Fields!City.Value = "New York", 1, Nothing) )
The count aggregate also takes a scope parameter, so depending on where you
use this expression in the report, you might need to add a scope for the
aggregate (e.g. the dataset name or containing reportitem name). E.g.:
=Count( iif(Fields!City.Value = "New York", 1, Nothing), "DataSet1" )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:7031923E-DD39-4A21-9721-F9CEF898D707@.microsoft.com...
> hi robert
> i am still confused
> i have a field but i dont want to use filter on the fields.city.value
> i want to count users where city is new york
> 1 field in my table is userid
> 1 field is their residing city
> what shall i do?
> "Robert Bruckner [MSFT]" wrote:
> > See BOL:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_3diq.asp
> >
> > Example:
> > Filter expression: =Fields!City.Value
> > Operator: => > Value expression: ="New York" or just New York (because it is a string)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Raj" <Raj@.discussions.microsoft.com> wrote in message
> > news:D5F8D505-BC13-4F20-887A-62BC1BF2A669@.microsoft.com...
> > > how do i filter any expression in the data region
> > > e.g. i want all users for just New York?
> > > how to apply that filter condition in the expression in data region?
> >
> >
> >|||i want to count the same thing
but count(nuserid) which is userid
in a text box in table
how will the box understand using this query
=Count( iif(Fields!City.Value = "New York", 1, Nothing), "DataSet1" )
that i want to count userid?
i dont have any parameters in my report
"Robert Bruckner [MSFT]" wrote:
> Sorry, but based on your questions I thought you want to filter data.
> However, you actually want to calculate some aggregates. Try this
> expression:
> =Count( iif(Fields!City.Value = "New York", 1, Nothing) )
> The count aggregate also takes a scope parameter, so depending on where you
> use this expression in the report, you might need to add a scope for the
> aggregate (e.g. the dataset name or containing reportitem name). E.g.:
> =Count( iif(Fields!City.Value = "New York", 1, Nothing), "DataSet1" )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Raj" <Raj@.discussions.microsoft.com> wrote in message
> news:7031923E-DD39-4A21-9721-F9CEF898D707@.microsoft.com...
> > hi robert
> > i am still confused
> > i have a field but i dont want to use filter on the fields.city.value
> > i want to count users where city is new york
> > 1 field in my table is userid
> > 1 field is their residing city
> > what shall i do?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > See BOL:
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_3diq.asp
> > >
> > > Example:
> > > Filter expression: =Fields!City.Value
> > > Operator: => > > Value expression: ="New York" or just New York (because it is a string)
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Raj" <Raj@.discussions.microsoft.com> wrote in message
> > > news:D5F8D505-BC13-4F20-887A-62BC1BF2A669@.microsoft.com...
> > > > how do i filter any expression in the data region
> > > > e.g. i want all users for just New York?
> > > > how to apply that filter condition in the expression in data region?
> > >
> > >
> > >
>
>

how to filter element by attribute to be imported using SQLXML schema annotation in XML Bu

[question 1]
does anyone know how to filter the element by attribute to be imported
using SQLXML annotation?
for example, in the following xml, I only want to import the value in
the element with attribute Type="shipping" only, not the one with
attribute Type="billing". is it possible to use annotation to filter
that?
<Addresses>
<Address Type="shipping">1234 shipping avenue</Address>
<Address Type="billing">5678 billing street</Address>
</Addresses>
[question 2]
This question is kind of related too. I'd like to write schema
annotaion that's both "element-centric" and "attribute-centric".
for example, in the the following xml, I need to upload both the value
in attribute CustID and also the value in element Customer.
<Customers>
<Customer CustID="1">Customer One</Customer>
<Customer CustID="2">Customer Two</Customer>
</Customers>
I've thought about using other approaches but XML Bulk Load should be
the ideal way for performance reason because our XML files are all
very big, more than 100MB. But we're stuck on the schema annotaion.
any idea you may have is greatly appreciated.
The short answer to Q1 is "no" I'm afraid. You could however use an XSLT
stylesheet to filter the data first, and then import the filtered data. The
XPath expression you need in the stylesheet is something like
"/Addresses[Address/@.Type='shipping']"
As for Q2, if the element/attribute names match the table/column names you
can actually get away without using a schema and both element-centric and
attribute-centric mappings will work by default. If the names are different
however, this won't work. Again, an XSLT stylesheet prior to bulk loading
would be the way to go here.
There's an example of using an XSLT stylesheet on sqlxml.org at
http://sqlxml.org/faqs.aspx?faq=49
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Evangeline" <evangeli@.yahoo.com> wrote in message
news:df3f63cc.0403282343.883270d@.posting.google.co m...
> [question 1]
> does anyone know how to filter the element by attribute to be imported
> using SQLXML annotation?
> for example, in the following xml, I only want to import the value in
> the element with attribute Type="shipping" only, not the one with
> attribute Type="billing". is it possible to use annotation to filter
> that?
> <Addresses>
> <Address Type="shipping">1234 shipping avenue</Address>
> <Address Type="billing">5678 billing street</Address>
> </Addresses>
>
> [question 2]
> This question is kind of related too. I'd like to write schema
> annotaion that's both "element-centric" and "attribute-centric".
>
> for example, in the the following xml, I need to upload both the value
> in attribute CustID and also the value in element Customer.
> <Customers>
> <Customer CustID="1">Customer One</Customer>
> <Customer CustID="2">Customer Two</Customer>
> </Customers>
>
>
> I've thought about using other approaches but XML Bulk Load should be
> the ideal way for performance reason because our XML files are all
> very big, more than 100MB. But we're stuck on the schema annotaion.
>
> any idea you may have is greatly appreciated.
|||1) No, I'm afraid you can't do that. As otherwise suggested, you could run
an XSLT first to remove the data you don't want.
2) You should be able to map this by annotating the element with the name of
the column you want it to go to and the attribute with the name of the
column you want it to go to.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Evangeline" <evangeli@.yahoo.com> wrote in message
news:df3f63cc.0403282343.883270d@.posting.google.co m...
> [question 1]
> does anyone know how to filter the element by attribute to be imported
> using SQLXML annotation?
> for example, in the following xml, I only want to import the value in
> the element with attribute Type="shipping" only, not the one with
> attribute Type="billing". is it possible to use annotation to filter
> that?
> <Addresses>
> <Address Type="shipping">1234 shipping avenue</Address>
> <Address Type="billing">5678 billing street</Address>
> </Addresses>
>
> [question 2]
> This question is kind of related too. I'd like to write schema
> annotaion that's both "element-centric" and "attribute-centric".
>
> for example, in the the following xml, I need to upload both the value
> in attribute CustID and also the value in element Customer.
> <Customers>
> <Customer CustID="1">Customer One</Customer>
> <Customer CustID="2">Customer Two</Customer>
> </Customers>
>
>
> I've thought about using other approaches but XML Bulk Load should be
> the ideal way for performance reason because our XML files are all
> very big, more than 100MB. But we're stuck on the schema annotaion.
>
> any idea you may have is greatly appreciated.
|||Thank you very much for your help.
I was trying to avoid using xslt because it adds too much overhead to the process.
for example, I'm using MSXML3 object to transform one of my file, the xslt process takes 70 seconds and the bulk load with translated xml only takes 50 seconds. It would be great if we could save that 70 seconds in xslt.
|||Can you move to MSXML4? The performance of XSLT there was greatly improved.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Evangeline" <anonymous@.discussions.microsoft.com> wrote in message
news:BAAC30DF-5073-48EC-9318-AFF09406224A@.microsoft.com...
> Thank you very much for your help.
> I was trying to avoid using xslt because it adds too much overhead to the
process.
> for example, I'm using MSXML3 object to transform one of my file, the xslt
process takes 70 seconds and the bulk load with translated xml only takes 50
seconds. It would be great if we could save that 70 seconds in xslt.
|||Thanks a lot. MSXML4 does cut the XSLT translation time to 40% less than MSXML3.
Just being curious, is there any other XML/XSLT component that has better performance than MSXML4? maybe some Java component?