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.
| Author |
Topic |
|
chava_sree
Yak Posting Veteran
56 Posts |
 Posted - 2008-10-23 : 18:40:35
|
| I've an XML document Inserted in a Varchar Column for a User Table, How do i Query the Column to get a Specific info from the XML Data.. as I am confused of how to read that either by Like or any other FUNCTION to read.my xml Looks like this and i need to get only CommunityAdministrator and the ID's of that Community Admin.--------------------------------------------------------------------<role><type>communityadministrator</type><id>19</id></role><role><type>communityadministrator</type><id>18</id></role><role><type>communityadministrator</type><id>16</id></role><role><type>communityblogger</type><id>35</id></role><role><type>user</type><id>141</id></role><role><type>communityblogger</type><id>141</id></role><role><type>user</type><id>14</id></role><role><type>communityblogger</type><id>14</id></role><role><type>user</type><id>147</id></role><role><type>user</type><id>106</id></role><role><type>user</type><id>165</id></role><role><type>user</type><id>167</id></role>Thanks |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-23 : 19:39:25
|
| You can always cast varchar as XML datatype. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-24 : 02:01:58
|
| two approaches. If sql 2005, best approach will be to cast it as xml dtatype or even use xml variable itself to store xml. then you can make use of xml functions avialable in sql 2005 like nodes(),query(),... to extract data from nodes of xml in variable.If you're using sql 2000 or earlier, xml datatype is not available and hence you've to use OPENXML function to extract data from xml.Refer to links below for more detailshttp://msdn.microsoft.com/en-us/library/ms345122.aspxhttp://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx |
|
|
|
|
|
|
Reading XML Data from Varchar Column