How to use sql function for a computed column to retrieve a value from XML

Question

I have a table with a XML column and save XML like this to it:

<Employees>
    <Person>
        <ID>1000</ID>
        <Name>Nima</Name>
        <LName>Agha</LName>
    </Person>   
</Employees>

I want to have another table with a columns that in it I use a function to retrieve value of Name element from every row.such this :

  Id           Name
  -----------------
  1            Nima
  2            Agha
  ...          ...

How I can do this?

thanks

Answer 1

Use like this:

CREATE FUNCTION dbo.GetName(@xml XML)
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN @xml.value('/Employees[1]/Person[1]/Name[1]', 'nvarchar(max)')
END
GO
SELECT dbo.GetName(CAST(N'
<Employees>
    <Person>
        <ID>1000</ID>
        <Name>Nima</Name>
        <LName>Agha</LName>
    </Person>   
</Employees>' AS XML))

But

you have to provide the connection between your Id field from second table to xml field from the first

Answer 2

Try to use a table valued function.

CREATE FUNCTION dbo.GetTableFromXML(@xml XML)
RETURNS @retXMLTable TABLE 
(
    -- Columns returned by the function
    ID int PRIMARY KEY NOT NULL, 
    Name nvarchar(max) NULL, 
    LName nvarchar(max) NULL, 
)AS
BEGIN
 INSERT @retXMLTable (ID,FirstName,LName)
 select @xml.value('/Employees[1]/Person[1]/ID[1]', 'nvarchar(max)'),
        @xml.value('/Employees[1]/Person[1]/Name[1]', 'nvarchar(max)') 
        @xml.value('/Employees[1]/Person[1]/LName [1]', 'nvarchar(max)') 
 RETURN;
END;

Generally same as the answer from Oleg, but you couuld work with the result as a table. If you recreate the sample you get get all your entries from the xml table at once.