How can I retrieve XML nodes dynamically from SQL Server and use XDocument to build a file from a template with them?

I have an XML template with all the headers I need. I load it with XDocument, and then build the nodes using XElements. However, this means I am currently hard coding the data bits I need to go into my file.

I noticed with SQL Server I can use the XML datatype to store data in the database. Using this approach it would be better to look through each record and dynamically build the XML file.

However, I see some issues with my approach. There may be two nodes that share the same parent. Is there a way to store the nodes and their parent, and use something in XDocument to merge them?

Here is a sample chunk of my XML

<AddInfoCollection>
    <AddInfo>
        <Key>TransportReference</Key>
        <Value>666777888</Value>
    </AddInfo>              
    <AddInfo>
        <Key>UI_NKCarrierSCAC</Key>
        <Value>ABCD</Value>
    </AddInfo>              
    <AddInfo>
        <Key>SchDLoading</Key>
        <Value>1234</Value>
    </AddInfo>  
    <AddInfo>
        <Key>SchDArrival</Key>
        <Value>12345</Value>
    </AddInfo>                  
</AddInfoCollection>

In the database, I'd imagine the table would be:

• Field (varchar(200)


• Value (varchar(200)


• XML

Sample:

Field                 Value          XML
----------------------------------------------------------------
TransportReference    666777888      <AddInfoCollection>
                                       <AddInfo>
                                         <Key>TransportReference</Key>
                                         <Value>666777888</Value>
                                       </AddInfo>   
                                     <AddInfoCollection>