本文介紹了XML 解析 - SQL Server的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
限時送ChatGPT賬號..
我有一個如下所示的 XML:
I have an XML that looks like below:
declare @xml xml = '<Margins >
<Margin type="type1" currencyCode="currencyCode1">
<MarginRevenue>1.1</MarginRevenue>
<MarginRevenue>1.2</MarginRevenue>
<MarginRevenue>1.3</MarginRevenue>
<MarginCost>2.1</MarginCost>
<MarginCost>2.2</MarginCost>
<MarginCost>2.3</MarginCost>
<MarginValue>3.1</MarginValue>
<MarginValue>3.2</MarginValue>
<MarginValue>3.3</MarginValue>
</Margin>
<Margin type="type2" currencyCode="currencyCode2">
<MarginRevenue>1.4</MarginRevenue>
<MarginRevenue>1.5</MarginRevenue>
<MarginRevenue>1.6</MarginRevenue>
<MarginCost>2.4</MarginCost>
<MarginCost>2.5</MarginCost>
<MarginCost>2.6</MarginCost>
<MarginValue>3.4</MarginValue>
<MarginValue>3.5</MarginValue>
<MarginValue>3.6</MarginValue>
</Margin>
<Margin type="type3" currencyCode="currencyCode3">
<MarginRevenue>1.7</MarginRevenue>
<MarginRevenue>1.8</MarginRevenue>
<MarginRevenue>1.9</MarginRevenue>
<MarginCost>2.7</MarginCost>
<MarginCost>2.8</MarginCost>
<MarginCost>2.9</MarginCost>
<MarginValue>3.7</MarginValue>
<MarginValue>3.8</MarginValue>
<MarginValue>3.9</MarginValue>
</Margin>
'
SELECT
[Margin_Revenue] = N.value('(MarginRevenue)[1]', 'decimal(15,5)')
,[Margin_Cost] = N.value('(MarginCost)[1]', 'decimal(15,5)')
,[Margin_Value] = N.value('(MarginValue)[1]', 'decimal(15,5)')
FROM
@xml.nodes('Margins/Margin') AS X(N)
我的要求是獲取所有 , 并且?guī)в?Path nodes('Margins/Margin') AS X(N).到目前為止，我只得到下面這實(shí)際上是每個 Margin 的第一條記錄:
My requirement is to get all the , and but with Path nodes('Margins/Margin') AS X(N). As of now, I'm getting below only which is actually the first record of each Margin:
Margin_Revenue Margin_Cost Margin_Value
1.10000 2.10000 3.10000
1.40000 2.40000 3.40000
1.70000 2.70000 3.70000
推薦答案
1:n 處有 數(shù)據(jù)位于 1:n 數(shù)據(jù)APPLY 使用 .nodes() 兩次.
There is 1:n data at <Margin> and again 1:n data at <MarginRevenue>. You need to use .nodes() twice via APPLY.
declare @xml xml = '<Margins>
<Margin type="type1" currencyCode="currencyCode1">
<MarginRevenue>1.1</MarginRevenue>
<MarginRevenue>1.2</MarginRevenue>
<MarginRevenue>1.3</MarginRevenue>
</Margin>
<Margin type="type2" currencyCode="currencyCode2">
<MarginRevenue>1.4</MarginRevenue>
<MarginRevenue>1.5</MarginRevenue>
<MarginRevenue>1.6</MarginRevenue>
</Margin>
<Margin type="type3" currencyCode="currencyCode3">
<MarginRevenue>1.7</MarginRevenue>
<MarginRevenue>1.8</MarginRevenue>
<MarginRevenue>1.9</MarginRevenue>
</Margin>
</Margins>'
SELECT
[Margin_Type] = Marg.value('@type', 'varchar(100)')
,[Margin_currencyCode] = Marg.value('@currencyCode', 'varchar(100)')
,[Revenue_Value] = Rev.value('text()[1]','decimal(15,5)')
FROM
@xml.nodes('Margins/Margin') AS A(Marg)
OUTER APPLY Marg.nodes('MarginRevenue') B(Rev);
結(jié)果
Type currencyCode Revenue_Value
-------------------------------------
type1 currencyCode1 1.10000
type1 currencyCode1 1.20000
type1 currencyCode1 1.30000
type2 currencyCode2 1.40000
type2 currencyCode2 1.50000
type2 currencyCode2 1.60000
type3 currencyCode3 1.70000
type3 currencyCode3 1.80000
type3 currencyCode3 1.90000
這篇關(guān)于XML 解析 - SQL Server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！