To select elements or attributes from XML columns, use the methods of the xml
data-type in SQL Server. Working example below:

DROP TABLE #employees
CREATE TABLE #employees ( employee_id INT IDENTITY PRIMARY KEY, surname
VARCHAR(50) NOT NULL, firstname VARCHAR(50) NOT NULL, start_date DATETIME )
GO

DROP TABLE #tmp
CREATE TABLE #tmp ( row_id INT IDENTITY PRIMARY KEY, column_xml XML )
GO

INSERT INTO #employees VALUES ( 'Lipa', 'Mical', '20080101' )
INSERT INTO #employees VALUES ( 'Bob', 'w', '20090101' )
GO

INSERT INTO #tmp
SELECT *
FROM
(
SELECT *
FROM #employees
FOR XML RAW
) x (y)

INSERT INTO #tmp
SELECT *
FROM
(
SELECT *
FROM #employees
FOR XML RAW, ELEMENTS
) x (y)
GO

SELECT *
FROM #tmp
GO

-- Select attribute
SELECT column_xml.value( '(/row/@start_date)[1]', 'VARCHAR(50)' )
FROM #tmp

-- Select element
SELECT column_xml.value( '(/row/start_date)[1]', 'VARCHAR(50)' )
FROM #tmp
GO

"micmixx@gmail.com" wrote: