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>';


EXEC sp_xml_preparedocument @handle OUTPUT, @XMLData

SELECT * FROM OPENXML (@handle, '/Messages/Message', 2)
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 .

No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author