Converting single column, single row XML data to SQL table

Question

I have a table called XMLTest, with a single column called Val with XML datatype

create table XMLTest(Val XML)

Into this table I read this data:


  
    
      
        2020-03-25
        2020-03-26
        
          130
          Currencies against Swedish kronor
          
            SEKEURPMI
            1 EUR
            1.0E0
            
              2020-03-25
              
              
              
              
              
              1.08823E1

So the final result is all of this data in a single column & single row. I've tried to extract the relevant data from this XML. I'm interested in seriesid & its value. So the final output would be:

seriesid | value
SEKEURPMI| 1.08823E1

and ultimately there will be multiple so the results would be more like:

seriesid | value
SEKEURPMI| 1.08823E1
SEKUSDPMI| 1.3823E1
....     | ...

However I'm having problems creating valid query to get this information. None of the solutions I've been trying work with mine because I'm querying directly from a table. I've been trying to implement this solution: How can I save data from xml to sql 2008? but I don't seem to be able to apply the same technique when querying from a table.

Gottfried Lesigang · Accepted Answer

Try it like this:

DECLARE @yourTable TABLE(ID INT IDENTITY,Val XML);
INSERT INTO @yourTable VALUES
(N'
  
    
      
        2020-03-25
        2020-03-26
        
          130
          Currencies against Swedish kronor
          
            SEKEURPMI
            1 EUR
            1.0E0
            
              2020-03-25
              
              
              
              
              
              1.08823E1
            
          
        
      
    
  
');

--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 , and 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 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 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.

Converting single column, single row XML data to SQL table

Answers (2)

Related Questions