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 Data Flow

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

ssiscoder wrote:

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

No you can't do this currently. Well, not with the out-of0the-box components anyway.

What you CAN do is build a package programatically in dotnet using the SSIS API. That dotnet code can be called from any place that can execute dotnet code. One of those places happens to be a SSIS package.

HTH.

-Jamie

|||

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

|||

ssiscoder wrote:

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

I can do better than that. here's a sample:

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

-Jamie

|||

This might help as well: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

-Jamie