Read XML in SSMS-CodePudding

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)

CodePudding user response：

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             |
 -------- --------------------- ---------------------