Read XML in SSMS

Question

I'm trying to produce an output that would give me both long-descriptions, i.e. one in German and one in English. It can be two records with 1 [long-description] column and one [lang] column, or 1 record with both [long-description-de] and [long-description-en] columns. So far I have found 2 methods, not sure which is better but I'm still not able to produce the exact output with either of them:

-- XML
DECLARE @idoc INT, @doc NVARCHAR(max);   
SET @doc ='  
<enfinity>
  <offer sku="123456">
    <sku>123456</sku>
    <long-description xml:lang="de-DE">German</long-description>
    <long-description xml:lang="en-US">English</long-description>
  </offer>
</enfinity>
';   
  


-- Method 1 
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   

SELECT *  
FROM   OPENXML (@idoc, '/enfinity/offer/long-description')   
WITH(               sku   int    '../sku',
               [long-description]      nvarchar(max)         '../long-description',
               lang         nvarchar(max)         '../@lang');
               


-- Method 2
DECLARE @T XML
SET @T = @doc

SELECT Y.ID.value('@sku', 'nvarchar(max)') as [sku],
        Y.ID.value('@long-description', 'nvarchar(max)') as [long-description-de],
        Y.ID.value('@long-description', 'nvarchar(max)') as [long-description-en]
FROM @T.nodes('/enfinity/offer') as Y(ID)

miriamka · Accepted Answer · 2021-09-13T15:19:50.867

0

Please try the following solution.

Important points:

It is better to use XML data type instead of NVARCHAR(MAX).
It is better not to use Microsoft proprietary OPENXML(). It was made for the now obsolete SQL Server 2000.
It is better to use XQuery methods .nodes() and .value(). They are available starting from MS SQL Server 2005 onwards.
It is important to distinguish XML element vs. attributes. That's why your both attempts didn't work.

SQL

DECLARE @doc XML = 
N'<enfinity>
  <offer sku="123456">
    <sku>123456</sku>
    <long-description xml:lang="de-DE">German</long-description>
    <long-description xml:lang="en-US">English</long-description>
  </offer>
</enfinity>';

SELECT c.value('@sku', 'nvarchar(max)') as [sku]
    , c.value('(long-description[@xml:lang="de-DE"]/text())[1]', 'nvarchar(max)') as [long-description-de]
    , c.value('(long-description[@xml:lang="en-US"]/text())[1]', 'nvarchar(max)') as [long-description-en]
FROM @doc.nodes('/enfinity/offer') as t(c);

Output

+--------+---------------------+---------------------+
|  sku   | long-description-de | long-description-en |
+--------+---------------------+---------------------+
| 123456 | German              | English             |
+--------+---------------------+---------------------+

edited Sep 13 '21 at 15:19

answered Sep 13 '21 at 15:13

miriamka

459
5
9

That's perfect thank you. Just out of curiosity what happens if I don't know what the lang will be? So for some data it might have Cantonese for example, is there a way to code it so we don't hardcode all the languages? – tommyhmt Sep 13 '21 at 15:33
You can add additional languages as columns, just their values will be NULLs. – miriamka Sep 13 '21 at 15:40
Sorry should have been more clear although I think I just answered my own question. I was hoping for something more eloquent like rather than hardcoding all possible languages, search for what languages are available first and only display them as columns. But I suspect that will only be possible with dynamic T-SQL to build up the query. – tommyhmt Sep 13 '21 at 16:07
on 2nd thought, is it possible to produce an output like this instead so I can dynamically pivot my result? ` +--------+------------------+---------+ | sku | long-description | lang | +--------+------------------+---------+ | 123456 | German | de-DE | +--------+------------------+---------+ | 123456 | English | en-US | +--------+------------------+---------+ ` – tommyhmt Sep 13 '21 at 16:56
Please ask a new question for the 2nd output, and I will gladly help you. – miriamka Sep 13 '21 at 18:05
no probs, I was just curious as it's not needed anyway, I have however just raised another question as the xml is more complicated: https://stackoverflow.com/questions/69167251/pivot-complex-xml-using-xquery – tommyhmt Sep 13 '21 at 18:06

Read XML in SSMS

1 Answers1