CODEWITHSUNDEEP
xmlxml-parsingopenxmlsql-server-openxmlopenxml-table
tommyhmt
tommyhmt

Reputation: 327

Read XML in SSMS

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)

Upvotes: 0

Views: 168

Answers (1)

miriamka
miriamka

Reputation: 481

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

Upvotes: 0

Related Questions