CODEWITHSUNDEEP
sql-serverxmlt-sqlpivot
Gonkas
Gonkas

Reputation: 13

Read XML columns in SQL

Trying to get the values and element names extracted from one XML column.

Values are getting just in one row and not able to extract the element name.

The elements in the cell are like this: 6161... And they are dynamically generated.

Here is the code:

 SELECT mainSku, r.value('.[1]','NVARCHAR(MAX)') AS 'value', r.query('.') AS 'secondarySku'
  FROM [productsMatrix] 
    CROSS APPLY details.nodes('/') AS x(r)
  WHERE mainSku = 'TP40106'

This is the wrong actual result

This is the result that is pretended

Thanks for reading :)

Upvotes: 1

Views: 668

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Your question is far away from being clear, but my magic crystall ball is showing, that you might be looking for this:

SELECT mainSku
      ,r.value('text()[1]','int') AS [value]
      ,r.value('local-name(.)') AS [secondarySku]
FROM [productsMatrix]
CROSS APPLY details.nodes('/*') AS x(r)
WHERE mainSku = 'TP40106'

Assumptions:

Your table [productsMatrix] has got an XML column called details. This column contains an XML with no root node, just a list of XML-Elements with names like <AC486>.

The CROSS APPLY on .nodes() will return a list of all first-level-nodes, while the query picks the content (text()-node) and the element's name.

Upvotes: 1

Related Questions