Dynamic child nodes in xml to columns in sql



I have a programming situation, where i need to select the dynamic child nodes in the xml into 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 child node Person can vary from 0 to n.


No comments:

Post a Comment