CODEWITHSUNDEEP
sqlxmlxquery
Packy
Packy

Reputation: 1

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

In SQL Server, how can I query the 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 the element names are the same, we can use the approach below:

Iterate through XML variable in SQL Server

Therefore, How to get the following result or something like that:

FirstName   |  LastName   | Age
----------- +-------------+-----
David       |   Jons      | 28
Eric        |   Terry     | 36
Kady        |   Campell   | 21

given that I don't know the element name of the XML such as FisrtName, LastName, Age,

I think I hope to get result like this SQL statement: 

SELECT * FROM Employee

in which I don't know the column name of table Employee

Upvotes: 0

Views: 71

Answers (1)

marc_s
marc_s

Reputation: 754220

Try something like 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')
FROM
    @input.nodes('/Employee') AS XT(XC)

That should give you an output of:

FirstName   LastName    Age
---------------------------
David       Jons         28
Eric        Terry        36
Kady        Campell      21

Upvotes: 1

Related Questions