June 9, 2011 at 6:08 am
when we load xml into cursor then we specify column name and their datatype and size in with block. instead of specifying manually how could i make that area dynamic. suppose my tsql as follows
Exec sp_xml_preparedocument @XMLFormat OUTPUT, @DetailXml
-- Create Cursor from XML Table
Declare CurDetailRecord
Cursor For
Select productid,unit,rate,qty,amount
From Openxml (@XMLFormat, '/NewDataSet/PurchaseOrderDetail', 2)
With
(
productid Varchar(10),
unit Varchar(50),
rate decimal(18,2),
qty decimal(18,3),
amount decimal(18,2)
)
here as a example
With
(
productid Varchar(10),
unit Varchar(50),
rate decimal(18,2),
qty decimal(18,3),
amount decimal(18,2)
)
etc i am specifying and also specify their data type & size.
there is a table called PurchaseOrderDetail and it has columns like
productid Varchar(10),
unit Varchar(50),
rate decimal(18,2),
qty decimal(18,3),
amount decimal(18,2)
can i query system table & fetch column details & build the with block dynamically.
please help me with sample thanks.
June 9, 2011 at 6:30 am
You can query information_Schema.columns to get column names and datatypes. Just be careful. If the structure of the table changes, but not the xml, then you will get an error
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply