我需要帮助从存储在 SQL Server 表的列中的 XML 中检索数据。我一直在搜索问题存储库,但找不到与我的匹配的任何问题。也许我以某种方式错过了。无论如何,这里存储的 XML 是这样的:
<ProductStructure>
<Plans>Essential</Plans>
<Plans>Standard</Plans>
<Plans>Silver</Plans>
<Plans>Gold</Plans>
<Plans>Platinum</Plans>
<Plans>Titanium</Plans>
<Destinations>Region A</Destinations>
<Destinations>Region B</Destinations>
<Destinations>Region C</Destinations>
<Destinations>Region D</Destinations>
<InsuredTypes>One Person</InsuredTypes>
<InsuredTypes>Couple</InsuredTypes>
<InsuredTypes>Group</InsuredTypes>
</ProductStructure>
我想选择 xml 并在 SQL 中获得如下输出:
|Plans |Essential |
| |Standard |
| |Silver |
| |Gold |
| |Platinum |
| |Titanium |
|---------------+-------------|
|Destinations |Region A |
| |Region B |
| |Region C |
| |Region D |
|---------------+-------------|
|InsuredTypes |One Person |
| |Couple |
| |Group |
对不起,如果问题被形象化了。谢谢你。
DECLARE @xml xml = N'<ProductStructure>
<Plans>Essential</Plans>
<Plans>Standard</Plans>
<Plans>Silver</Plans>
<Plans>Gold</Plans>
<Plans>Platinum</Plans>
<Plans>Titanium</Plans>
<Destinations>Region A</Destinations>
<Destinations>Region B</Destinations>
<Destinations>Region C</Destinations>
<Destinations>Region D</Destinations>
<InsuredTypes>One Person</InsuredTypes>
<InsuredTypes>Couple</InsuredTypes>
<InsuredTypes>Group</InsuredTypes>
</ProductStructure>'
SELECT
t.value('local-name(.)','nvarchar(max)'),
t.value('.','nvarchar(max)')
FROM @xml.nodes('ProductStructure/*') AS t(t)
你会得到:
-------------------- --------------------
Plans Essential
Plans Standard
Plans Silver
Plans Gold
Plans Platinum
Plans Titanium
Destinations Region A
Destinations Region B
Destinations Region C
Destinations Region D
InsuredTypes One Person
InsuredTypes Couple
InsuredTypes Group
您可以根据需要对结果进行分组。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句