SQL Server XML属性切碎

Question

我目前正在运行SQL Server 2014，需要从提供的XML字符串中分解出属性/子属性，为"年龄"、"颜色"、"座位"one_answers"传输类型"创建列：

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

我向发件人验证了这是他们发送XML的唯一方法，而无需大量的变通方法，所以我只能使用给定的XML格式。

编辑：我正在寻找一个返回数据集，其中有一列年龄，一列颜色，一列座位颜色，等等。

我运行的SQL Server版本：Microsoft SQL Server 2014(SP2-GDR((KB4057120(-12.0.5214.6(X64(2018年1月9日15:03:12版权所有(c(Microsoft Corporation Standard Edition(64位(on Windows NT 6.3(Build 9600:(

Answer 1

SQL Server版本没有答案。我假设它是SQL Server 2016或更高版本，支持JSON。

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;

输出

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