Get specific XML Child node in SQL Server
<p>I am trying to load XMl to different tables with logical keys in MS SQL Server. 
I am stuck as I am getting all child nodes in SQL.</p>

<pre><code>&lt;Books&gt;
&lt;Book&gt;
    &lt;Name&gt;Book1&lt;/Name&gt;
    &lt;Author&gt;abc&lt;/Author&gt;
    &lt;Stores&gt;
        &lt;Name&gt;Amazon&lt;/Name&gt;
    &lt;/Stores&gt;
&lt;/Book&gt;
&lt;Book&gt;
    &lt;Name&gt;Book2&lt;/Name&gt;
    &lt;Author&gt;cde&lt;/Author&gt;
    &lt;Stores&gt;
        &lt;Name&gt;Flipkart&lt;/Name&gt;
    &lt;/Stores&gt;
&lt;/Book&gt;
&lt;/Books&gt;
</code></pre>

<p>I want to get result as below.</p>

<pre><code>BookId  Name   Author  StoreXML
1       Book1  abc     &lt;Stores&gt;&lt;Name&gt;Amazon&lt;/Name&gt;&lt;/Stores&gt;  
2       Book2  cde     &lt;Stores&gt;&lt;Name&gt;Flipkart&lt;/Name&gt;&lt;/Stores&gt;
</code></pre>