Converting single column, single row XML data to SQL table

Try it like this:

DECLARE @yourTable TABLE(ID INT IDENTITY,Val XML);
INSERT INTO @yourTable VALUES
(N'<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope">
  <SOAP-ENV:Body>
    <ns0:getInterestAndExchangeRatesResponse xmlns:ns0="http://swea.riksbank.se/xsd">
      <return xmlns="">
        <datefrom xmlns="">2020-03-25</datefrom>
        <dateto xmlns="">2020-03-26</dateto>
        <groups xmlns="">
          <groupid xmlns="">130</groupid>
          <groupname xmlns="">Currencies against Swedish kronor</groupname>
          <series xmlns="">
            <seriesid xmlns="">SEKEURPMI</seriesid>
            <seriesname xmlns="">1 EUR</seriesname>
            <unit xmlns="">1.0E0</unit>
            <resultrows xmlns="">
              <date xmlns="">2020-03-25</date>
              <period xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <min xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <average xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <max xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <ultimo xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <value xmlns="">1.08823E1</value>
            </resultrows>
          </series>
        </groups>
      </return>
    </ns0:getInterestAndExchangeRatesResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>');

--the query

SELECT t.ID
      ,A.gr.value('(groupid/text())[1]','int') AS groupId
      ,A.gr.value('(groupname/text())[1]','nvarchar(max)') AS groupName
      ,B.sr.value('(seriesid/text())[1]','nvarchar(max)') AS seriesId
      ,B.sr.value('(seriesname/text())[1]','nvarchar(max)') AS seriesName
      ,C.rw.value('(date/text())[1]','date') AS rowDate
      ,C.rw.value('(period/text())[1]','nvarchar(max)') AS rowPeriod
      ,C.rw.value('(value/text())[1]','float') AS rowValue
FROM @yourTable t
CROSS APPLY t.Val.nodes('//groups') A(gr)
OUTER APPLY A.gr.nodes('series') B(sr)
OUTER APPLY B.sr.nodes('resultrows') C(rw);

The idea in short and some background:

• The plural form of <groups>, <series> and <resultrows> lets me think, that this is nested, 1:n related data, hence the cascade of APPLY ...nods().
• This XML has a mess of wrong namespaces...
• It would be awfully ugly to define/declare all namespaces properly...
• Gladfully alle the values are living in an empty default namespace.
• To avoid the namespaces I start with //groups. The double // triggers a deep search. Don't follow this suggestion, if an element <groups> might occure anywhere else!
• We get all groups
• We get all series within each group
• We get all rows within each serie

Hint: about the ns1:nil="true". Assumingly this was generated with T-SQL using ELEMENTS XSINIL. XML would omit NULL values by default. That means, that the reader should know the schema. Asking for a missing element will then return NULL. but in this case all elements are generated. We can test if the nil attribute is true. But in this case it is easier to test for the text() node. This is missing and will implicitly return NULL.

Some remarks about namespaces:

• The namespaces to name the SOAP related parts are okay, the namespace to specify the element <getInterestAndExchangeRatesResponse> is okay too.
• But the often used xmlns="" is really odd. This will define an empty default namespace again and again...
• I assume that this is generated with T-SQL using nested FOR XML calls and by stuffing such a pre-generated content XML into the SOAP envelope.