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