BizTalk Utilities CV ,   Jobs ,   Code library
 
Go to the front page to continue learning about XML or select below:

Contents

ReBlogger Contents

Previous posts in SQLXML

 
 
CONTENTS
Page 6070 of 19542

Handy SQL Delimited string to XML

Blogger : Geekswithblogs.net
All posts : All posts by Geekswithblogs.net
Category : SQLXML
Blogged date : 2007 Dec 11

I came across this simple yet great way of translating a delimited string to XML using SQL 2005.

http://www.sqlservercentral.com/articles/XML/61618/

 

/*
Here is the string that we need to split
*/
DECLARE @str VARCHAR(100)
SET @str = '0001,0002,0003,0004,0005'

/*
I am converting the string to an XML structure by
inserting XML tags.
*/
DECLARE @x XML
SET @x = '<i>' + REPLACE(@str, ',', '</i><i>') + '</i>'

/*
Now we can apply XQuery to return a resultset
*/
SELECT x.i.value('.', 'VARCHAR(4)') AS Item
 FROM @x.nodes('//i') x(i)

/*
Item
----
0001
0002
0003
0004
0005

(5 row(s) affected)
*/

Read comments or post a reply to : Handy SQL Delimited string to XML
CONTENTS
Page 6070 of 19542
Newest posts
WCF LOB | BizTalk 2006 R2 | WSE | WSCF/WCF | BizTalk Rule Engine | Biztalk Adapters | Orchestration / Workflow | BizTalk 2006 | BizTalk 2004 | BizTalk 2002 | BizTalk Server | SOA | SQLXML | XmlSerializer | WS, web services | .NET XML, System.XML | XSL/T | XML |
 

    Email TopXML