How to use sql function for computed column to extract value from XML

Question

I have a table with an XML column and save the XML like this:

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

I want to have another table with columns in which I use a function to extract the value of the Name element from each row. This:

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

How can i do this?

thanks

Answer 1

Use 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 need to specify the connection between your Id field from the second table in the xml field from the first

Answer 2

Try using 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; 

In general, the same as the answer from Oleg, but you can work with the result in the form of a table. If you recreate the sample, you will immediately receive all your entries from the xml table.