SQL XML Column - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL XML Column

Author

Topic

IK1972

56 Posts

Posted - 2012-08-24 : 02:00:32

In One Column I have Data Like this.

<ID>345</ID><ID>5665</ID><ID>7877</ID>

And I want to display in another column with comma separated like 345,5665,7877

Thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-24 : 04:46:35

If column data is not properly in XML format then just use REPLACE function to get your desired results

DECLARE @MyValue VARCHAR(100)
SET @MyValue ='<ID>345</ID><ID>5665</ID><ID>7877</ID>'
SELECT REPLACE(REPLACE(REPLACE(@MyValue,'</ID><ID>',','),'<ID>',''),'</ID>','')

If data is in proper XML format then go for OPENXML() function
http://msdn.microsoft.com/en-us/library/ms186918.aspx

--------------------------
http://connectsql.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 07:20:44

An alternative, if the data is truly XML would be as follows:

DECLARE @MyValue XML;
SET @MyValue ='<ID>345</ID><ID>5665</ID><ID>7877</ID>'
SELECT REPLACE(CAST(@MyValue.query('data(/ID)') AS VARCHAR(100)),' ',',')

If the XML structure is as simple as what you have indicated in the example, almost certainly the method Lion posted is simpler and probably more efficient as well. But, if your XML structure is more complex using XML query gives you more flexibility.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources