sql - XML 解析 - SQL Server
问题描述
我有一个如下所示的 XML:
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)
我的要求是获取所有的,但使用路径节点('Margins/Margin')AS X(N)。截至目前,我只得到以下实际上是每个保证金的第一条记录:
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
数据,在 处<Margin>
有1:n
数据<MarginRevenue>
。您需要.nodes()
通过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);
结果
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
推荐阅读
- python - Python SpeechRecognition Can't Find File
- authentication - Google Cloud Endpoints pass API key in header
- javascript - Print table information after onclick event in Django
- docker-compose - How can I use docker-compose in a bitbucket pipeline to deploy to a remote host?
- python - Random number generation in Tensorflow is very slow (tensorflow-gpu)
- excel - How to convert time stamp with text into a date
- java - Apache Artemis 客户端故障转移发现
- flutter - Where is the best place to put your ChangeNotifierProvider?
- spring-boot - The Maven Command With spring-boot:spring-image Doesn't Work As What It Should Do
- ios - iOS 应用程序在更改已撤销的隐私设置后重新启动