整理網路上找到的資料，並套用
目前也還不知道怎麼讓他顯示更複雜的階層架構
把結果跟 Json.Net 產生的相比，其實有漏掉資料
想一次把所有查詢結果全部以 Json 方式回傳，看來還有一段路要走
建立 SQL Function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 25/10/2014
-- Description: www.4sln.com
-- =============================================
CREATE FUNCTION dbo.fn_XmlToJson_Get
(
@XmlData XML
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN
(SELECT STUFF(
(SELECT
*
FROM
(SELECT
',{'+
STUFF(
(SELECT
',"'+
COALESCE(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+ b.c.value('text()[1]','NVARCHAR(MAX)') +'"'
FROM x.a.nodes('*') b(c) FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
+'}'
FROM @XmlData.nodes('/root/*') x(a)) JSON(theLine)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,''))
END
GO
實際執行結果，經過 https://jsonformatter.curiousconcept.com/ 整理
{
"OrderID":"10248",
"CustomerID":"VINET",
"EmployeeID":"5",
"OrderDate":"1996-07-04T00:00:00",
"RequiredDate":"1996-08-01T00:00:00",
"ShippedDate":"1996-07-16T00:00:00",
"ShipVia":"3",
"Freight":"32.3800",
"ShipName":"Vins et alcools Chevalier",
"ShipAddress":"59 rue de l'Abbaye",
"ShipCity":"Reims",
"ShipPostalCode":"51100",
"ShipCountry":"France"
},
{
"OrderID":"10249",
"CustomerID":"TOMSP",
"EmployeeID":"6",
"OrderDate":"1996-07-05T00:00:00",
"RequiredDate":"1996-08-16T00:00:00",
"ShippedDate":"1996-07-10T00:00:00",
"ShipVia":"1",
"Freight":"11.6100",
"ShipName":"Toms Spezialitäten",
"ShipAddress":"Luisenstr. 48",
"ShipCity":"Münster",
"ShipPostalCode":"44087",
"ShipCountry":"Germany"
},
{
"OrderID":"10250",
"CustomerID":"HANAR",
"EmployeeID":"4",
"OrderDate":"1996-07-08T00:00:00",
"RequiredDate":"1996-08-05T00:00:00",
"ShippedDate":"1996-07-12T00:00:00",
"ShipVia":"2",
"Freight":"65.8300",
"ShipName":"Hanari Carnes",
"ShipAddress":"Rua do Paço, 67",
"ShipCity":"Rio de Janeiro",
"ShipRegion":"RJ",
"ShipPostalCode":"05454-876",
"ShipCountry":"Brazil"
}