Parsing Xml Into Sql With Elements At Different Levels Of Xml Tree

I have a problem where I need to parse a bunch of XML files into a SQL database where the needed elements are all in different branches of the XML file. Every examples that I have

Solution 1:

YoOu need to use nodes on your XML data and CROSS APPLY. This appear to get you what you're after:

USE Sandbox;
GO
DECLARE @XML xml = '<store>
  <bookstore>
    <book>
      <ref_title>
        <title>Harry Potter</title>
      </ref_title>
      <ref_author>
       <author>J K. Rowling</author>
      </ref_author>
      <year>
        <this_year>2005</this_year>
      </year>
      <price>
        <dollar>usd</dollar>
        <value>29.99</value>
      </price>
      <price>
        <dollar>aud</dollar>
        <value>49.99</value>
      </price>
    </book>
    <book>
      <ref_title>
        <title>Petes Book of Pie</title>
      </ref_title>
      <ref_author>
       <author>Pete P</author>
      </ref_author>
      <year>
        <this_year>1999</this_year>
      </year>
      <price>
        <dollar>usd</dollar>
        <value>19.99</value>
      </price>
      <price>
        <dollar>aud</dollar>
        <value>39.99</value>
      </price>
    </book>
  </bookstore>
</store>';

SELECT bs.b.value('(ref_title/title/text())[1]','nvarchar(50)') AS Title,
       bs.b.value('(ref_author/author/text())[1]','nvarchar(50)') AS Author,
       bs.b.value('(year/this_year/text())[1]','nvarchar(50)') AS [Year],
       p.d.value('(./text())[1]','nvarchar(50)') AS Dollar,
       p.d.value('(../value/text())[1]','nvarchar(50)') AS [Value]
FROM (VALUES(@XML))V(X)
     CROSS APPLY V.X.nodes('/store/bookstore/book') bs(b)
     CROSS APPLY bs.b.nodes('price/dollar') p(d)
WHERE p.d.value('(./text())[1]','nvarchar(50)') = 'usd';