Xml Parsing On Sql Server

How to return data in format: Column Name t01 t02 t03 t04 Data c01 c02 c03 c04

Solution 1:

From your question there's one thing not clear: The naming of the output columns.

In your expected output they are named like their attribute-id. But in your comments it sounds, like you are picking the first 4 attributes and you want to omit the rest.

I want to show two approaches, pick the one you like more:

DECLARE @mockupTable TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @mockupTable VALUES
(N'<ordersxmlns="www address"><order><order-date>2019-09-05</order-date><created-by>storefront</created-by><original-order-no>000001</original-order-no><currency>USD</currency><taxation>gross</taxation><invoice-no>0099999</invoice-no><custom-attributes><custom-attributeattribute-id="t01">c01</custom-attribute><custom-attributeattribute-id="t02">c02</custom-attribute><custom-attributeattribute-id="t03">c03</custom-attribute><custom-attributeattribute-id="t04">c04</custom-attribute></custom-attributes></order></orders>');

--This query will use the attribute-id to pick the corresponding attribute. --We can savely return this with the same name --If your XML does not have the corresponding attribute, there will be a NULL value

WITH XMLNAMESPACES(DEFAULT'www address')
SELECT o.value('(order-date/text())[1]','date') OrderDate
      --As in your other questions
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t01"]/text())[1]','varchar(100)') AS t01 
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t02"]/text())[1]','varchar(100)') AS t02
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t03"]/text())[1]','varchar(100)') AS t03 
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t04"]/text())[1]','varchar(100)') AS t04 
FROM@mockupTable t
CROSS APPLY t.YourXml.nodes('/orders/order') A(o);

--This one is easier. It will pick just the first four attributes, no matter what id they have.

WITH XMLNAMESPACES(DEFAULT'www address')
SELECT o.value('(order-date/text())[1]','date') OrderDate
      --As in your other questions
      ,o.value('(custom-attributes/custom-attribute[1]/text())[1]','varchar(100)') AS ca1 
      ,o.value('(custom-attributes/custom-attribute[2]/text())[1]','varchar(100)') AS ca2
      ,o.value('(custom-attributes/custom-attribute[3]/text())[1]','varchar(100)') AS ca3 
      ,o.value('(custom-attributes/custom-attribute[4]/text())[1]','varchar(100)') AS ca4 
FROM@mockupTable t
CROSS APPLY t.YourXml.nodes('/orders/order') A(o);

Share