How do you read XML Data in SQL Server 2005 [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and c开发者_如何学Pythonannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I have a table which has XMLData column as TEXT. How can i read the data from this column

---

As long as that column is of type TEXT, you won't be able to do anything useful with it, really. TEXT also has been deprecated and will be removed in a future version of SQL Server - stop using it.

If it stores XML and only XML - make it of datatype XML.

One you have that, you can either extract individual items of information from that XML using XPath and XQuery - something like:

SELECT
    YourXMlColumn.value('(/Root/SomeItems/Item/FirstName)[1]', 'varchar(50)') as 'FirstName',
    YourXMlColumn.value('(/Root/SomeItems/Item/Age)[1]', 'int') as 'Age'
FROM
    dbo.YourTable
WHERE
    (some condition)

or if you have multiple items in a list-like structure inside your XML, you can create a "pseudo-table" of XML items based on an XPath expression.

So your plan of action should be:

1. make this column use the appropriate datatype - XML
2. tell us in more detail what kind of XML you have stored in there, and what you want to get from that XML

---

Make your column an XML datatype instead of TEXT. The following MSDN article is a lengthy description of how XML is supported in sql server:

http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx