Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-02 : 07:32:03
|
| ajay writes "I was checking out the xsd.exe tool. First I run a SQL Query with FOR XML AUTO, I save this as a file with .xdr extension. Then I run xsd.exe and generate a c# class that matches the xsd schema. C:\>xsd /c /l:cs orders.xsdMicrosoft (R) Xml Schemas/DataTypes support utility[Microsoft (R) .NET Framework, Version 1.1.4322.573]Copyright (C) Microsoft Corporation 1998-2002. All rights reserved.Writing file 'C:\orders.cs'. This would account for generating a business object straight from an sql query/database! Now, I was just wondering if the reverse was possible ? i.e., assuming I already have a Business Object (or I would generate one as shown above), can I pass this business object to a stored procedure, which would do an update/insert on the appropriate tables based on the business object. Also can we directly pass a business object/s to a stored proc ? or should it be transformed into some other form before such a thing can be done ? I am looking for a solution in which the stored proc can accept a business object or an array of business objects and commit changes in the appropriate tables. Any help or pointers would be of great help." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-02 : 13:18:37
|
| A "Business Object" doesn't really mean anything to SQL Server. It's either XML or not, with an optional schema definition.You can pass XML into a stored procedure, but if it exceeds 8,000 bytes it gets a little tricky. Anything over 8,000 bytes would need to be passed as a text paramter, which cannot be altered, and therefore must be passed as a literal value. And getting XML from a file into SQL Server is also problematic.In any case, you'd want to look in Books Online under "sp_xmlpreparedocument" for more information on how to have SQL Server parse XML documents or fragments. Also take a look on Microsoft's site about the SQLXML utilities:http://www.microsoft.com/sql/techinfo/xml/default.aspAnd if you have Ken Henderson'sThe Guru's Guide to SQL Server Stored Procedures, XML, and HTMLHe has extensive chapters on how to use XML with SQL Server, amongst other excellence. If you do not have this book, buy it today. You'll love it.You also have the option of reformatting your XML data into flat text format, and importing it into the appropriate tables using bcp or BULK INSERT. If you're moving a lot of data around bcp/BULK INSERT will be much faster than XML. |
 |
|
|
|
|
|
|
|
Passing Bussiness Object to Stored Proc