title: Extract data from XML document using t-sql
body: <p>I have been trying to extract data from the following xml doc using t-sql on sql server 2019.</p>
  <h2>XML:</h2>
  <pre><code>&lt;urlset xmlns=&quot;http://www.sitemaps.org/schemas/sitemap/0.9&quot; xmlns:image=&quot;http://www.google.com/schemas/sitemap-image/1.1&quot;&gt;
    &lt;url&gt;
      &lt;loc&gt;https://www.URL1.com/1&lt;/loc&gt;
      &lt;image:image&gt;
        &lt;image:loc&gt;https://www.URL1.com/11&lt;/image:loc&gt;
      &lt;/image:image&gt;
      &lt;image:image&gt;
        &lt;image:loc&gt;https://www.URL1.com/12&lt;/image:loc&gt;
      &lt;/image:image&gt;
      &lt;image:image&gt;
        &lt;image:loc&gt;https://www.URL1.com/13&lt;/image:loc&gt;
      &lt;/image:image&gt;
    &lt;/url&gt;
    &lt;url&gt;
      &lt;loc&gt;https://www.URL1.com/2&lt;/loc&gt;
      &lt;image:image&gt;
        &lt;image:loc&gt;https://www.URL1.com/21&lt;/image:loc&gt;
      &lt;/image:image&gt;
      &lt;image:image&gt;
        &lt;image:loc&gt;https://www.URL1.com/22&lt;/image:loc&gt;
      &lt;/image:image&gt;
    &lt;/url&gt;
    &lt;url&gt;
      &lt;loc&gt;https://www.URL1.com/3&lt;/loc&gt;
      &lt;image:image&gt;
        &lt;image:loc&gt;https://www.URL1.com/32&lt;/image:loc&gt;
      &lt;/image:image&gt;
    &lt;/url&gt;
  &lt;/urlset&gt;
  </code></pre>
  <p>I would like to extract data out of the xml document into a SQL Server table. My desired output as below</p>
  <h2>Desired output:</h2>
  <pre><code>+------------------------+-------------------------+
  |          Loc           |        ImageLoc         |
  +------------------------+-------------------------+
  | https://www.URL1.com/1 | https://www.URL1.com/11 |
  | https://www.URL1.com/1 | https://www.URL1.com/12 |
  | https://www.URL1.com/1 | https://www.URL1.com/13 |
  | https://www.URL1.com/2 | https://www.URL1.com/21 |
  | https://www.URL1.com/2 | https://www.URL1.com/22 |
  | https://www.URL1.com/3 | https://www.URL1.com/32 |
  +------------------------+-------------------------+
  </code></pre>
  <p>My Attempts have been failed so far miserably. I have tried many thing but the only thing that allowed me to get even the Loc element was the following, I have tried using OUTER APPLY/CROSS APPLY to het the ImageLoc with no luck.</p>
  <h2>My Attempt:</h2>
  <pre><code>DECLARE @xml XML
  SELECT @xml = BulkColumn
  FROM OPENROWSET(BULK 'M:\Files\MyXML.xml', SINGLE_BLOB) x
  
  SELECT
      t.c.value('(text())[1]', 'VARCHAR(max)') URLs
  ,   t2.i.value('(text())[1]', 'VARCHAR(max)') URLs
  
  FROM @xml.nodes('*:urlset/*:url/*:loc') t(c)
  OUTER APPLY  @xml.nodes('*:urlset/*:url/*:loc/*:image/*:loc') t2(i)
  </code></pre>
  <p>Could you please help? Thanks in advance</p>