Business scenario: We have an XML file (HIPAA 837) saved as a XML data column in a table and we need to go through each row and get different information on different nodes and output to a table. The real reason of this design lies in with how the claim system is designed, which I will not try to explain here.

Approach:

Using Common Table Expression (CTE) to get all the necessary info so that the data can be joined later.

Use ROW_NUMBER() to make sure the information comes from the correct line.

Journey:

Had to use Cross Apply because the value is stored in the attribute instead of the node

Could not use Cross Apply for all fields in the same select because rows get duplicated

Could not use cursor because [xmldatatype].query() only takes string as parameter.

If someone can read up on this and make suggestions, I'd greatly appreciate it, because I really didn't like the fact that I had to go into the same table 5 times just to get the 5 different columns