-1

I have to create queries on a SQL Server database that stores the relevant data attributes for each line in a single XML column. The list of attributes in that column is formatted like this:

<Attributes>
    <A N="FRUIT">APPLE</A>
    <A N="COLOR">RED</A>
</Attributes>

I have to parse that column so that instead I have a column for each attribute (So I'd have a column for FRUIT and one for COLOR). I'm aware of some shredding methods, but I don't know how to reformat the column so that the attribute names can be extracted.

Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
Void Fish
  • 1
  • 1
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Aug 06 '21 at 15:13

1 Answers1

1

Please try the following solution.

It is based on use of a couple of XQuery methods: .nodes() and .value()

SQL

-- DDL and data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<Attributes>
    <A N="FRUIT">APPLE</A>
    <A N="COLOR">RED</A>
</Attributes>');
-- DDL and data population, end

SELECT ID
    , c.value('(A[@N="FRUIT"]/text())[1]', 'VARCHAR(30)') AS FRUIT
    , c.value('(A[@N="COLOR"]/text())[1]', 'VARCHAR(30)') AS COLOR
FROM @tbl
    CROSS APPLY xmldata.nodes('/Attributes') AS t(c);

Output

+----+-------+-------+
| ID | FRUIT | COLOR |
+----+-------+-------+
|  1 | APPLE | RED   |
+----+-------+-------+
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21