This article shows how to extract data from an XML column in SQL Server. Extracting data from an XML column in SQL Server can be accomplished using a variety of built-in functions and methods.

Example

Here is a step-by-step guide on how to extract data from an XML column in SQL Server:

1. Create a table with an XML column:

CREATE TABLE MyTable (Id int, Data xml);

2. Insert some XML data into the table:

INSERT INTO MyTable (Id, Data)
VALUES (1, '<Person><Name>John</Name><Age>30</Age></Person>');
INSERT INTO MyTable (Id, Data)
VALUES (2, '<Person><Name>Jane</Name><Age>28</Age></Person>');

3. Use the value() method to extract data from the XML column:

SELECT Data.value('(Person/Name)[1]', 'varchar(50)') AS Name,
Data.value('(Person/Age)[1]', 'int') AS Age
FROM MyTable
WHERE Id = 1;

In this example, the value() method is used to extract the values of the Name and Age elements from the XML column. The first argument of the value() method is an XQuery expression that identifies the location of the data to extract. The second argument is the data type of the extracted value.