XML : Taking information from an XML node in t-sql no matter the amount

XML 101 - day 1

I have a column with xml in each row. Each piece of xml follows the same template. I wish to extract information from a node no matter how many entries there are in it.

Example:

  <CodeLists>    <CodeList CodeListID="FAC8BFB9-7C41-4647-A6DC-DF359379B8C3">      <Code Number="1">L1CCTT</Code>      <Code Number="2">L2CCTT</Code>      <Code Number="3">L3CCTT</Code>    </CodeList>    <CodeList CodeListID="F0328C4F-916B-414D-AA81-D59244A4F0E1">      <Code Number="1">16 to 18</Code>      <Code Number="2">19 to 25</Code>      <Code Number="3">26 to 49</Code>      <Code Number="4">50 plus</Code>    </CodeList>    

I wish only to return L1CCTT, L2CCTT, L3CCTT.

However it coule be that the xml on the second row has more or less than 3 codes.

Example:

  <CodeLists>    <CodeList CodeListID="DA730346-2425-439D-ABD0-7FFF1F16ED3E">      <Code CodeNumber="1">L1CCA</Code>      <Code CodeNumber="2">L2CCA</Code>    </CodeList>    <CodeList CodeListID="7B9731F5-C1B2-460D-B78F-F7C64A959022">      <Code CodeNumber="1">16 to 18</Code>      <Code CodeNumber="2">19 to 25</Code>      <Code CodeNumber="3">26 to 49</Code>      <Code CodeNumber="4">50 plus</Code>    </CodeList>    

Example:

  <CodeLists>    <CodeList CodeListID="74E6C8E2-BD88-4CB6-B578-DD25EC532CAA">      <Code Number="1">CIT33C</Code>      <Code Number="2">CIT32C</Code>      <Code Number="3">CIT31C</Code>      <Code Number="4">CIT30C</Code>    </CodeList>    <CodeList CodeListID="C6957FC8-F3B4-4DAE-A8D0-F79A253C5790">      <Code Number="1">16 to 18</Code>      <Code Number="2">19 to 25</Code>      <Code Number="3">26 to 49</Code>      <Code Number="4">50 plus</Code>    

I have produced something but I begin receive things from outside the code lists node and have had no luck with devising a where clause or node specific code.

Columns: ProjectID, Data

SELECT [Data].value('(/CodeLists/CodeList/Code)[1]', 'varchar(4000)') [Code1] ,[Data].value('(/CodeLists/CodeList/Code)[2]', 'varchar(4000)') [Code2] ,[Data].value('(/CodeLists/CodeList/Code)[3]', 'varchar(4000)') [Code3] ,[Data].value('(/CodeLists/CodeList/Code )[4]', 'varchar(4000)') [Code4] ,[Data].value('(/CodeLists/CodeList/Code )[5]', 'varchar(4000)') [Code5] FROM [dbo].Codes WHERE ProjectID = 1

Apologies if this is a simple task however any help would be greatly appreciated.