Showing posts with label complex. Show all posts
Showing posts with label complex. Show all posts

Sunday, March 11, 2012

convert table data into complex xml document

I would like to convert the following two tables into the XML document below
without a bunch of UNIONS. The reason being in my case i have hundereds of
tables w/ different fields all going into a single XML document. Is there
any way to use user defined functions or something to construct just one XML
document? Or is there a way i can convert all the foos into a string, all
the bars into a string etc. then concatinate them and return them as an xml
document?
TABLE FOO
x y
________________ ____________________
1 11
2 22
TABLE BAR
a b
________________ ____________________
3 33
4 44
Using Open XML how do you convert this into:
<rootfoobar>
<foos>
<foo x='1' y='11'/>
<foo x='2' y='22'/>
</foos>
<bars>
<bar a='3' b='33'/>
<bar a='4' b='44'/>
</bars>
</rootfoobar>
The UNIONS are not so bad (the mainteance of the query is rather ugly
though).
In SQL Server 2000, you either use the mapping schemas of the mid-tier
SQLXML component or FOR XML EXPLICIT queries.
If the data has no correlation, you can also use the XML templates in SQLXML
to put individual query results together.
In SQL Server 2005, you will be able to nest FOR XML and use the new PATH
mode that avoids the UNION all. See my blog entries and links to an MSDN
whitepaper on http://sqljunkies.com/weblog/mrys
HTH
Michael
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%23l2gkbrbEHA.3716@.TK2MSFTNGP11.phx.gbl...
>I would like to convert the following two tables into the XML document
>below
> without a bunch of UNIONS. The reason being in my case i have hundereds of
> tables w/ different fields all going into a single XML document. Is there
> any way to use user defined functions or something to construct just one
> XML
> document? Or is there a way i can convert all the foos into a string, all
> the bars into a string etc. then concatinate them and return them as an
> xml
> document?
> TABLE FOO
> x y
> ________________ ____________________
> 1 11
> 2 22
>
>
> TABLE BAR
> a b
> ________________ ____________________
> 3 33
> 4 44
>
> Using Open XML how do you convert this into:
> <rootfoobar>
> <foos>
> <foo x='1' y='11'/>
> <foo x='2' y='22'/>
> </foos>
> <bars>
> <bar a='3' b='33'/>
> <bar a='4' b='44'/>
> </bars>
> </rootfoobar>
>
>

Friday, February 10, 2012

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,
I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
David Portas
SQL Server MVP

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
David Portas
SQL Server MVP
--

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
--
David Portas
SQL Server MVP
--