sql server - Dynamic child nodes in xml to columns in sql -

sql server - Dynamic child nodes in xml to columns in sql -

i have programming situation, need select dynamic kid nodes in xml individual columns in sql server query.

for ex,.

<persons> <id>1</id> <person> <firstname>jon</firstname> <lastname>johnson</lastname> </person> <person> <firstname>kathy</firstname> <lastname>carter</lastname> </person> <person> <firstname>bob</firstname> <lastname>burns</lastname> </person> </persons>

the select should return,

id firstname lastname firstname lastname firstname lastname 1 jon johnson kathy carter bob burns

this kid node person can vary 0 n.

sql-server xml