Iterate through XML variable in SQL Server whether what is in XML -

in sql server, how can query following xml

<employee>    <firstname>david</firstname>    <lastname>jons</lastname>    <age>28</age> </employee> <employee>    <firstname>eric</firstname>    <lastname>terry</lastname>    <age>36</age> </employee> <employee>    <firstname>kady</firstname>    <lastname>campell</lastname>    <age>21</age> </employee> 

if element names same, can use approach below:

iterate through xml variable in sql server

therefore, how following result or that:

firstname   |  lastname   | age ----------- +-------------+----- david       |   jons      | 28 eric        |   terry     | 36 kady        |   campell   | 21 

given don't know element name of xml such fisrtname, lastname, age,

i think hope result sql statement:

select * employee 

in don't know column name of table employee

try this:

declare @input xml = '<employee>    <firstname>david</firstname>    <lastname>jons</lastname>    <age>28</age> </employee> <employee>    <firstname>eric</firstname>    <lastname>terry</lastname>    <age>36</age> </employee> <employee>    <firstname>kady</firstname>    <lastname>campell</lastname>    <age>21</age> </employee>'  select     firstname = xc.value('(firstname)[1]', 'varchar(50)'),     lastname = xc.value('(lastname)[1]', 'varchar(50)'),     age = xc.value('(age)[1]', 'int')     @input.nodes('/employee') xt(xc) 

that should give output of:

firstname   lastname    age --------------------------- david       jons         28 eric        terry        36 kady        campell      21