CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008view
DooDoo
DooDoo

Reputation: 13467

Create a view based on XML field

I have table with a XML field that has specific constant format.How I can create a View based on this field that show me data in this field?

thanks

EDIT 1)

My Data is like this:

enter image description here

Upvotes: 0

Views: 2192

Answers (2)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

You can create something like this

CREATE VIEW [dbo].[vEmployees]
WITH SCHEMABINDING
AS
    SELECT
    person.n.value('ID[1]', 'int') AS ID,
    person.n.value('Name[1]', 'nvarchar(50)') AS Name,
    person.n.value('LastName[1]', 'navarchar(50)') AS LastName
    FROM dbo.Table x
    CROSS APPLY x.XmlColumn.nodes('/Employees/Person') person(n)

GO

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

It is the same as creating any view. 

create view vName
as
select somecolumn
from sometable

Just insert your query that uses the XML column to get your values instead.

Upvotes: 1

Related Questions