Monday, April 6, 2009

How to Use New XML Datatype of sql server to pass data in SQL Server Instead Comma Separated values.

Hi Friends..
Working with SQL database we often need to pass comma separated values for many reasons.
eg.Inserting multiple record ,Updating Multiple records or deleting multiple records.
In such conditon we generally think of Comma separated values.which will be separated by looping in SQL server.and used as per our case. Instead of this you can Use XML string .SQL Server 2005 Supports XML Data tyape using read XML file and XML string Using XQuery..
Lets See How??
declare @XMLData as XML

set @XMLData='<Messages><Message><ID>94</ID><RID>1</RID><Message>HI</Message><PostedDate>2009-04-06T01:35:26.437</PostedDate></Message><Message><ID>95</ID><RID>1</RID><Message>HUA</Message><PostedDate>2009-04-06T01:36:26</PostedDate></Message><Message><ID>96</ID><RID>1</RID><Message>Majama?</Message><PostedDate>2009-04-06T01:37:26</PostedDate></Message><Message><ID>97</ID><RID>1</RID><Message>HA</Message><PostedDate>2009-04-06T01:38:26</PostedDate></Message></Messages>';


DECLARE @handle INT

EXEC sp_xml_preparedocument @handle OUTPUT, @XMLData

SELECT * FROM OPENXML (@handle, '/Messages/Message', 2)
WITH ( ID VARCHAR(20),
RID NVARCHAR(20),
PostedDate NVARCHAR(20)
) tmpSqlTable

tmpSqlTable you Will get all data in XML String as SQL Table.
you can use this table for joining with other table or inserting data in sql table..
Hope This Will Help You .
Thanks....