Parsing XML data using Transact-SQL in SQL Server 2014

I need to parse the XML data contained in the code shown below. What should the five lines of code that follow the SELECT statement be, please?

I have read the section on XML in the book SQL Server 2014 by Frederic Brouard et al. Based on this reading, I tried entering different things in the five lines of code, but they did not work.

DECLARE @ForexXML

SET @ForexXML = N'

<rates>
  <date2019-07-23>
    <USD>0.7605336601</USD>
  </date2019-07-23>
  <date2019-07-25>
    <USD>0.7617709547</USD>
  </date2019-07-25>
  <date2019-07-26>
    <USD>0.758719346</USD>
  </date2019-07-26>
  <date2019-07-24>
    <USD>0.7616052506</USD>
  </date2019-07-24>
  <date2019-07-22>
    <USD>0.7647459939</USD>
  </date2019-07-22>
</rates>
<start_at>2019-07-20</start_at>
<base>CAD</base>
<end_at>2019-07-26</end_at>'

INSERT INTO dbo.destinationTable (Date, Base, Currency, Rate)
    SELECT
        Node.Dt.value('SUBSTRING(date[1], 5, 10)', 'DATE') AS Date,
        Node.Dt.value('.', 'NVARCHAR(10)') AS Base,
        Node.Dt.value('date[1]/@USD', 'NVARCHAR(20)') AS Currency,
        Node.Dt.value('.', 'NVARCHAR(MAX)') AS Rate
    FROM 
        @ForexXMLData.nodes('.') Node(Dt)

Expected result:

The parsed data is to go into an SQL Server table with the following four columns, each having the values shown:

Date          Base    Currency    Rate
----------------------------------------------
2019-07-23    CAD     USD         0.7605336601
2019-07-25    CAD     USD         0.7617709547
2019-07-26    CAD     USD         0.758719346
2019-07-24    CAD     USD         0.7616052506
2019-07-22    CAD     USD         0.7647459939

The actual results so far:

Date         Base         Currency   Rate
---------------------------------------------
1900-01-01   0.76053366   NULL       0.76053366010.76177095470.7587193460.76160525060.76474599392019-07-20CAD2019-07-26

Solution

Try this:

SELECT
    Date = CAST(SUBSTRING(xc.value('local-name(.)', 'nvarchar(25)'), 5, 15) AS DATE),
    Base = @ForexXMLData.value('(base)[1]', 'varchar(10)'),
    Currency = xc2.value('local-name(.)', 'nvarchar(25)'),
    ExchangeRate = xc.value('.', 'decimal(20, 12)')
FROM
    @ForexXMLData.nodes('/rates/*') AS XT(XC)
CROSS APPLY
    XC.nodes('*') AS XT2(XC2)

The first call to .nodes() gets a list of XML fragments for each nodes inside the <rates> element - those <date2019-07-23> elements (this is btw a horribly bad design - to encapsulate the date value into the name of the XML element; it would be much easier and cleaner if that would be an attribute - something like <date value="2019-07-23"> or something like that!).

The second call to .nodes() in the CROSS APPLY then gets all the sub-elements for each of those <dateXXXXX> elements - those are your <USD> elements, and prints out first their element name (local-name(.)), and also their value.