CODEWITHSUNDEEP
sql-serverxml
Jayrunner13
Jayrunner13

Reputation: 13

SQL Server XML Attribute Shredding

I'm currently running SQL Server 2014 and need to shred out attributes/sub attributes from a provided XML string, creating columns for Age, Color, Seating, and Transmission Type:

DECLARE @XmlInput xml;
SELECT @XmlInput = '<Cars><Car Description="(Camry) {Age=2, Color=Blue, Seating=5, TransmissionType=Automatic}"/><Car Description="(Fusion) {Age=4, Color=Red, Seating=5, TransmissionType=Automatic}"/></Cars>';
SELECT x.y.value('(@Description)[1]','varchar(1000)') AS CarDescr FROM @XmlInput.nodes('/Cars/Car') AS x(y);

I verified with the sender that this is the only way they can send XML without large workarounds on their end so I am stuck with the given XML format.

Edits: I am looking for a return data set with a column for age, a column for color, a color for seating, etc.

I am running on SQL Server version: Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) - 12.0.5214.6 (X64) Jan 9 2018 15:03:12 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )

Upvotes: 0

Views: 127

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

There is no answer on the SQL Server version. I am assuming it is SQL Server 2016 or later with JSON support.

SQL

DECLARE @XmlInput XML = 
N'<Cars>
    <Car Description="(Camry) {Age=2, Color=Blue, Seating=5, TransmissionType=Automatic}"/>
    <Car Description="(Fusion) {Age=4, Color=Red, Seating=5, TransmissionType=Automatic}"/>
</Cars>';

;WITH rs AS
(
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(
            c.value('(@Description)[1]','varchar(1000)')
            ,'{','{"')
            ,'}','"}')
            ,'=','":"')
            ,', ','", "') AS CarDescr 
    FROM @XmlInput.nodes('/Cars/Car') AS t(c)
), cte AS
(
    SELECT LEFT(CarDescr, CHARINDEX(SPACE(1), CarDescr) - 1) AS Model
        , RIGHT(CarDescr, LEN(CarDescr) - CHARINDEX(SPACE(1), CarDescr)) AS [json]
        , * 
    FROM rs
)
SELECT SUBSTRING(Model, 2, LEN(Model)-2) AS [Model]
    , JSON_VALUE([json], '$.Age') AS [Age]
    , JSON_VALUE([json], '$.Color') AS [Color]
    , JSON_VALUE([json], '$.Seating') AS [Seating]
    , JSON_VALUE([json], '$.TransmissionType') AS [TransmissionType]
    , cte.json
FROM cte;

Output

+--------+-----+-------+---------+------------------+----------------------------------------------------------------------------+
| Model  | Age | Color | Seating | TransmissionType |                                    json                                    |
+--------+-----+-------+---------+------------------+----------------------------------------------------------------------------+
| Camry  |   2 | Blue  |       5 | Automatic        | {"Age":"2", "Color":"Blue", "Seating":"5", "TransmissionType":"Automatic"} |
| Fusion |   4 | Red   |       5 | Automatic        | {"Age":"4", "Color":"Red", "Seating":"5", "TransmissionType":"Automatic"}  |
+--------+-----+-------+---------+------------------+----------------------------------------------------------------------------+

Upvotes: 2

Related Questions