Search code examples
sqlsql-server-2005sqlxml

Get a list of fields out of an XML data type

Given a table structure like such:

DECLARE @Employees TABLE(EmployeeID INT ,EmployeeDetails XML)

INSERT INTO @Employees( EmployeeID ,EmployeeDetails )
VALUES ( 1 ,'<Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee>')

INSERT INTO @Employees
( EmployeeID ,EmployeeDetails )
VALUES ( 2 ,'<Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age> 28 </Age><Address> Australia</Address></EmployeeDetails></Employee>')

How can I create a query to get a list of all tag NAMES (not values) underneath the EmployeeDetails tag? (Assuming the structure is the same for all records).

So, desired result would be:

EmployeeName
Age
Address

Sam

Solution

  • Try something like this:

    SELECT
    DISTINCT T.C.value('local-name(.)[1]', 'varchar(100)')
FROM @Employees
CROSS APPLY EmployeeDetails.nodes('Employee/EmployeeDetails/*') AS T(C)