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...
>

Customize Report Parameter Properties

Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.No, this is not possible.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> Is it possible to modify the XML code at runtime? I want to control the
> report parameter properties multi-value setting of True/False during
> runtime
> in the XML behind the RDL file. Set it to True if I want the parameters to
> be
> multi-value or False for single-value. This is determined based on the
> value
> selected in parameter one. Parameter one and two are City,State.
> If City is selected in parameter one then I want Parameter two to be a
> single-valued list, if State is chosen in Parameter One then I want the
> list
> in Parameter two to be a multi-valued select list.
> Any ideas or guidance would be appreciated.
>|||Looking at my example, could you recommend an approach to tackle this issue?
"Bruce L-C [MVP]" wrote:
> No, this is not possible.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> > Is it possible to modify the XML code at runtime? I want to control the
> > report parameter properties multi-value setting of True/False during
> > runtime
> > in the XML behind the RDL file. Set it to True if I want the parameters to
> > be
> > multi-value or False for single-value. This is determined based on the
> > value
> > selected in parameter one. Parameter one and two are City,State.
> >
> > If City is selected in parameter one then I want Parameter two to be a
> > single-valued list, if State is chosen in Parameter One then I want the
> > list
> > in Parameter two to be a multi-valued select list.
> >
> > Any ideas or guidance would be appreciated.
> >
>
>|||It is a nice concept but this is not an area you have control over if you
are using the portal (report manager) that ships with Reporting Services. If
you want to create your own web page and then integrate with RS using either
web services or url integration this is certainly possible. RS 2005 ships
with a webform and a winform control for integrating RS reports into your
applications.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
> Looking at my example, could you recommend an approach to tackle this
> issue?
> "Bruce L-C [MVP]" wrote:
>> No, this is not possible.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
>> > Is it possible to modify the XML code at runtime? I want to control the
>> > report parameter properties multi-value setting of True/False during
>> > runtime
>> > in the XML behind the RDL file. Set it to True if I want the parameters
>> > to
>> > be
>> > multi-value or False for single-value. This is determined based on the
>> > value
>> > selected in parameter one. Parameter one and two are City,State.
>> >
>> > If City is selected in parameter one then I want Parameter two to be a
>> > single-valued list, if State is chosen in Parameter One then I want the
>> > list
>> > in Parameter two to be a multi-valued select list.
>> >
>> > Any ideas or guidance would be appreciated.
>> >
>>|||Thank you for your reply. So if I create a separate web page, can I reference
this page as a link from Report Manager going out and launching this custom
page/report? Would I find the winform/webform via Visual Studio?
"Bruce L-C [MVP]" wrote:
> It is a nice concept but this is not an area you have control over if you
> are using the portal (report manager) that ships with Reporting Services. If
> you want to create your own web page and then integrate with RS using either
> web services or url integration this is certainly possible. RS 2005 ships
> with a webform and a winform control for integrating RS reports into your
> applications.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
> > Looking at my example, could you recommend an approach to tackle this
> > issue?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No, this is not possible.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> >> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> >> > Is it possible to modify the XML code at runtime? I want to control the
> >> > report parameter properties multi-value setting of True/False during
> >> > runtime
> >> > in the XML behind the RDL file. Set it to True if I want the parameters
> >> > to
> >> > be
> >> > multi-value or False for single-value. This is determined based on the
> >> > value
> >> > selected in parameter one. Parameter one and two are City,State.
> >> >
> >> > If City is selected in parameter one then I want Parameter two to be a
> >> > single-valued list, if State is chosen in Parameter One then I want the
> >> > list
> >> > in Parameter two to be a multi-valued select list.
> >> >
> >> > Any ideas or guidance would be appreciated.
> >> >
> >>
> >>
> >>
>
>|||Well, the only way you could do this is to have a report that just has
links. You would need to have your own website. Usually people either use
Report Manager or provide their own front end via their own application.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:EFC6D25C-E313-4398-92F6-1C5651FABFB9@.microsoft.com...
> Thank you for your reply. So if I create a separate web page, can I
> reference
> this page as a link from Report Manager going out and launching this
> custom
> page/report? Would I find the winform/webform via Visual Studio?
> "Bruce L-C [MVP]" wrote:
>> It is a nice concept but this is not an area you have control over if you
>> are using the portal (report manager) that ships with Reporting Services.
>> If
>> you want to create your own web page and then integrate with RS using
>> either
>> web services or url integration this is certainly possible. RS 2005 ships
>> with a webform and a winform control for integrating RS reports into your
>> applications.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
>> > Looking at my example, could you recommend an approach to tackle this
>> > issue?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No, this is not possible.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> >> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
>> >> > Is it possible to modify the XML code at runtime? I want to control
>> >> > the
>> >> > report parameter properties multi-value setting of True/False during
>> >> > runtime
>> >> > in the XML behind the RDL file. Set it to True if I want the
>> >> > parameters
>> >> > to
>> >> > be
>> >> > multi-value or False for single-value. This is determined based on
>> >> > the
>> >> > value
>> >> > selected in parameter one. Parameter one and two are City,State.
>> >> >
>> >> > If City is selected in parameter one then I want Parameter two to be
>> >> > a
>> >> > single-valued list, if State is chosen in Parameter One then I want
>> >> > the
>> >> > list
>> >> > in Parameter two to be a multi-valued select list.
>> >> >
>> >> > Any ideas or guidance would be appreciated.
>> >> >
>> >>
>> >>
>> >>
>>

Custom xml format with FOR XML EXPLICIT

I'm working on Sql Server 2005 xml capabilities, but I'm not able to obtain what I want.

Let's say I have a table with 10 fields: Field1, Field2, ..., Field10.

I would, with a "FOR XML" clause, obtain an xml document like this one:

<MyTable>
<Field1>F1value</Field1>
<Field2 Field3="F3value">F2value</Field2>
</MyTable>
<MyTable>
...
</MyTable>

I think correct way is with the "EXPLICIT" mode, but I'm not able to find the syntax to make Field3 become an attribute of the element generated from Field2.

Does someone knows if it's possible and, if it is, how?

Any help will be truly appreciated.

Easier to do this

select Field1 as "Field1",
Field3 as "Field2/@.Field3",
Field2 as "Field2"
from MyTable
for xml path('MyTable')

|||Thank you so much!

This is exactly what I was looking for.sql

custom xml data type

When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.
Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

custom xml data type

When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

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