Customized Schema

Hi,
Is there any way to specify an XML Schema to SQL Server so when i use "For XML", SQL Server return data in XML Format in the Schema i have defined.
For example. I need sql server to return XML as
<object name="<table_name>">
<property name="<column_name>" value="<column_value>"/>
<property name="<column_name" value="<column_value>"/>
</object>
is it possible ? is there any other work aournd ?
Thanks in advance.
Regards,
Hatim Ali.
You should be able to do that with For XML Explicit. Another option would
be to create a mapping schema and use the XPath query from SQLXML to do the
queries for you. This also uses For XML Explicit but the XPath logic
generates the query so you don't have to.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Hatim Ali" <HatimAli@.discussions.microsoft.com> wrote in message
news:63B74B0A-E902-45F8-9255-4293BD18C70F@.microsoft.com...
> Hi,
> Is there any way to specify an XML Schema to SQL Server so when i use "For
> XML", SQL Server return data in XML Format in the Schema i have defined.
> For example. I need sql server to return XML as
> <object name="<table_name>">
> <property name="<column_name>" value="<column_value>"/>
> <property name="<column_name" value="<column_value>"/>
> </object>
> is it possible ? is there any other work aournd ?
> Thanks in advance.
> Regards,
> Hatim Ali.
|||Roger's solution works if you want <colname>value</colname> or exactly now
all your column names in advance.
We do not provide a general pivoting mechanism in T-SQL for SQL Server 2000
(and the PIVOT in 2005 has some limits).
If you need a completely generic way, you best use FOR XML for getting the
<colname>value</colname> format and then use an XSLT stylesheet to transform
it into the generic form below.
Best regards
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eIfu5RzaEHA.3480@.TK2MSFTNGP11.phx.gbl...
> You should be able to do that with For XML Explicit. Another option would
> be to create a mapping schema and use the XPath query from SQLXML to do
> the queries for you. This also uses For XML Explicit but the XPath logic
> generates the query so you don't have to.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Hatim Ali" <HatimAli@.discussions.microsoft.com> wrote in message
> news:63B74B0A-E902-45F8-9255-4293BD18C70F@.microsoft.com...
>

Custom Order By...Revisited

Hi

I would like to return rows in a custom order, similar to this post. However, in my case I do not have a fixed number of 'sort by' items. The order I would like to have returned from Col2 is:

'A' Numeric Values in numeric order 0-99 All other alpha values

For example:
'A', '0', '1', '3', '65', 'MyValue1', 'MyValue2'

How could I achieve this?

thanks

Richard

Here You Go...

Code Snippet

Create Table #sorttest (

[Col2] Varchar(100)

);

Insert Into #sorttest Values('A');

Insert Into #sorttest Values('12');

Insert Into #sorttest Values('1');

Insert Into #sorttest Values('3');

Insert Into #sorttest Values('5');

Insert Into #sorttest Values('6');

Insert Into #sorttest Values('78');

Insert Into #sorttest Values('100');

Insert Into #sorttest Values('MyValue1');

Insert Into #sorttest Values('MyValue3');

Insert Into #sorttest Values('MyValue2');

Select

*

From

#sorttest

Order By

Case When Col2 Like '[A-Z]' Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2

/*

::Output

Col2

-

A

1

3

5

6

12

78

100

MyValue1

MyValue2

MyValue3

*/

|||hi, try this

SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) = 0
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE ISNUMERIC(MyMixedField) = 1
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) > 0
and ISNUMERIC(MyMixedField) <> 1|||i think manivannan's solution is much better,

@.manivannan..

it think its better to use patindex('%[0-9]%', col2) = 0 for alpha valued records

Order By

Case When patindex('%[0-9]%', col2) = 0 Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2|||thank you both very much for your quick and accurate responses!

Works perfectly
|||

It seems that manivannan's suggested use of

Like '[A-Z]'

is most likely the best option to sort on single letter alpha. Don't you think that the

patindex('%[0-9]%', col2) = 0

suggestion 'might' include things like an asterisk, period, @. symbol, $ symbol, etc.? (-As well as allowing multi-character alpha entries on the first sort level...)

However, I would caution that using manivannan's suggestion to use isnumeric() 'could' cause problems. For example, the following evaluates to TRUE

SELECT isnumeric( '$' )

But is it a number? Should it sort with the alphas?

|||Hi

Good point, but actually in this case we don't allow non-alphanumeric characters in this field, so it's not really an issue!

thanks everyone for help on this

Custom Formatting SQLXML Result

Here's what am doing:
I have a couple of SPs that can only return data in the format i want when i use FOR XML EXPLICIT.
The problem is if am using SQLXML then the data cannot be returned as XML since formatting is done by the client (which only gives me the option of raw/nested) and therefore the result i get is not formatted as i need it.
Is there a way i can format the result from the SP in the same way as if i were just using FOR XML EXPLICIT on the server side.

Example of output formatting expected:
<Patients_List>
<patient>
<Field name="forename"/>
<Field name="surname">Patience</Field>
</patient>
<patient>
<Field name="forename">Adam</Field>

<Field name="surname">Sandler</Field>
</patient>
...
</Patients_List>

My problem is, the raw/nested options cannot format the output as above (using FOR XML EXPLICIT could achieve this).

Any suggestions?

Are you using SQL2005? If so please give me your table structure. I might help you to write FOR XML RAW/nested query.|||Am actually using SQL 2000.

sample table structure.

Table Name: pat_info

id int
surname varchar(35)
forename varchar(35)
dob datetime
occupation varchar(100)

not sure if the structure helps (its just part of table).

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

Hope this helps,

Monica

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

http://msdn2.microsoft.com/en-us/library/ms171802.aspx

Hope this helps,

Monica

Custom Code to Setup Parameter

I have a function that calcualte last_sunday date and return a datetime.

I have two Parameter in my report

start_date

end_date

and I put th following code in the report Parameer Properties

for Start date parameter

code.Last_Sunday

For End Date Parameter

code.Last_Sunday.adddays(6)

However when I ry to Preview I am getting this error

[rsReportParameterPropertyTypeMismatch] The property ‘DefaultValue’ of report parameter ‘Start_Date’ doesn't have the expected type.

[rsReportParameterPropertyTypeMismatch] The property ‘DefaultValue’ of report parameter ‘End_Date’ doesn't have the expected type.

Build complete -- 2 errors, 0 warnings

What I am doign Wrong both parameter are defined as datetime.

Thanks

Tanweer

Public Function Last_Sunday() As datetime

Dim Processed_date As DateTime
Dim start_date As DateTime
' Dim End_date As DateTime
Processed_date = DateTime.Today

'start_date = DateAdd(Processed_date - Weekday(Processed_date) + 1)
Select Case Processed_date.DayOfWeek.ToString
Case "Monday"
start_date = Processed_date.AddDays(-8)
Case "Tuesday"
start_date = Processed_date.AddDays(-9)
Case "Wednesday"
start_date = Processed_date.AddDays(-10)
Case "Thursday"
start_date = Processed_date.AddDays(-11)
Case "Friday"
start_date = Processed_date.AddDays(-12)
Case "Saturday"
start_date = Processed_date.AddDays(-13)
Case "Sunday"
start_date = Processed_date.AddDays(-14)
End Select
' End_date = format(start_date.AddDays(6),"mm/dd/yyyy")
console.writeline (start_date)
Return format(start_date,"MM/dd/yyyy")
End Function

You may want to try changing this line:

Return format(start_date,"MM/dd/yyyy")

to

CDate(Return format(start_date,"MM/dd/yyyy"))

The format function returns a string - not a DateTime...

Why are you formatting it here?

You mention a report...would it not be better to just pass the unformatted datetime to your report and handle the formatting in the report?

If you want create a re-usable function like this, it is better to return the raw data and handle formatting etc from where it is called. It is more generic and re-useable that way.

Steve

Custom Code problem!

Hi,
I want to use Custom Code to control the Visibility of a Group.
I write a test code, it always Return True:
Function CanSeen() as Boolean
Return True
End Function
Then I set the initial visibility of a group to Expression =Code.CanSeen()
But the the group can't seen!!
What is the problem?Are you calling your custom code from the row properties? If so, which
property? The "hidden" one? If so, notice that RS is asking you "do you want
this row to be hidden?" and you're saying "True". Perhaps that's the point.
I hope this helps
--
Please mark the correct/helpful answers!
"ad" wrote:
> Hi,
> I want to use Custom Code to control the Visibility of a Group.
> I write a test code, it always Return True:
> Function CanSeen() as Boolean
> Return True
> End Function
> Then I set the initial visibility of a group to Expression =Code.CanSeen()
> But the the group can't seen!!
> What is the problem?
>
>
>

Custom Aggregation Functions like SUM, AVERAGE etc.

Hi,

I want to write a custom aggregation function called PRODUCT (as it exists in Excel) to be used with [Measures].[Monthly Return] column. I could not see such function in AggregateFunction attribute of the mesaure in the Cube explorer.

What are the different ways I can write my own Aggregation functions? OR Achieve similar functionality with any alternative approach?

Appreciate your response.. let me know if further information is required.

-Ashish

You can't write your own Aggregation functions but you can certainly achieve the same result using MDX Script assignments to control how measures roll up. Regarding your specific problem, there is a function in the Analysis Services Stored Procedure project which does exactly what you're looking for:

http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=Multiplication

...although my understanding is that using a sproc in your MDX Script could have an adverse effect on caching, so you might want to test out using the sproc and the technique using logs which is described by Darren Gosbell here:

http://geekswithblogs.net/darrengosbell/archive/2006/07/18/85539.aspx

HTH,

Chris

Cursors and Remote Stored Procedures

given it would appear i can use a cusor as an OUTPUT parameter to a remote
stored procedure, what is the best way to retrieve/return a batch of data
from a stored procedure.On Tue, 8 Nov 2005 07:26:06 -0800, "Martin Samm" <Martin
Samm@.discussions.microsoft.com> wrote:

>given it would appear i can use a cusor as an OUTPUT parameter to a remote
>stored procedure, what is the best way to retrieve/return a batch of data
>from a stored procedure.
Hi Martin,
http://www.sommarskog.se/share_data.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)