创建一个查询，使用筛选器以更快的速度将数据提取为XML
创建一个查询，使用筛选器以更快的速度将数据提取为XML
提问于 2020-08-06 15:24:29
我正在使用SQL server 2014，试图以分层结构从SQL server获取xml。
WITH
parent as (select ModelId,ParentIdFK,ModelName,Expanded,SortOrder from model where NodeLevel = 0),
FirstNode as (select ModelId,ParentIdFK,ModelName,Expanded,SortOrder from model where ParentIdFK in(select ModelId from parent)),
SecondNode as (select ModelId,ParentIdFK,ModelName,Expanded,SortOrder from model where ParentIdFK in(select ModelId from FirstNode)),
ThirdNode as (select ModelId,ParentIdFK,ModelName,Expanded,SortOrder from model where ParentIdFK in(select ModelId from SecondNode)),
FouthNode as (select ModelId,ParentIdFK,ModelName,Expanded,SortOrder from model where ParentIdFK in(select ModelId from ThirdNode)),
FifthNode as (select ModelId,ParentIdFK,ModelName,Expanded,SortOrder from model where ParentIdFK in(select ModelId from FouthNode)),
SixthNode as (select ModelId,ParentIdFK,ModelName,Expanded,SortOrder from model where ParentIdFK in(select ModelId from FifthNode)),
XmlData as (select (select p.*,L1.*,L2.*,L3.*,L4.*,L5.*,L6.* from parent p
left join FirstNode L1 on L1.ParentIdFK =p.ModelId
left join SecondNode L2 on L2.ParentIdFK=L1.ModelId
left join ThirdNode L3 on L3.ParentIdFK=L2.ModelId
left join FouthNode L4 on L4.ParentIdFK=L3.ModelId
left join FifthNode L5 on L5.ParentIdFK=L4.ModelId
left join SixthNode L6 on L6.ParentIdFK=L5.ModelId
for xml auto , ROOT('ModelLines'),type) as XMLDataModel)
(select @data = XMLDataModel from XmlData)`
我得到了outPut
<model ModelId="11" ParentIdFK="3" ModelName="Sedans" Expanded="0" SortOrder="1">
<model ModelId="14" ParentIdFK="11" ModelName="328i Sedan" Expanded="0" SortOrder="1">
<model>
<model />
</model>
</model>
<model ModelId="15" ParentIdFK="11" ModelName="328xi Sedan" Expanded="0" SortOrder="2">
<model>
<model />
</model>
</model>
<model ModelId="16" ParentIdFK="11" ModelName="335i Sedan" Expanded="0" SortOrder="3">
<model>
<model />
</model>
</model>
<model ModelId="167" ParentIdFK="11" ModelName="Sheilas Model" Expanded="0" SortOrder="3">
<model>
<model />
</model>
</model>
<model ModelId="289" ParentIdFK="11" ModelName="335xi Sedan" Expanded="0" SortOrder="3">
<model>
<model />
</model>
</model>
</model>
<model ModelId="12" ParentIdFK="3" ModelName="Sports Wagon" Expanded="0" SortOrder="2">
<model ModelId="17" ParentIdFK="12" ModelName="328xi Sports Wagon" Expanded="0" SortOrder="1">
<model>
<model />
</model>
</model>
<model ModelId="18" ParentIdFK="12" ModelName="328i Sports Wagon" Expanded="0" SortOrder="2">
<model>
<model />
</model>
</model>
<model ModelId="214" ParentIdFK="12" ModelName="Convertible" Expanded="0" SortOrder="4">
<model ModelId="223" ParentIdFK="214" ModelName="328i Convertible" Expanded="0" SortOrder="1">
<model />
</model>
<model ModelId="224" ParentIdFK="214" ModelName="335i Convertible" Expanded="0" SortOrder="3">
<model />
</model>
</model>
</model>这个查询也是2，但是有很多节点没有属性。
所以我试着用set @data.modify('delete //model[empty(@ModelId)]')来删除这些节点--这需要30多个节点。有谁能建议一种更快更好的方法来更快地获取xmal？
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-08-06 16:24:38
使用：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
namespace ConsoleApplication164
{
class Program
{
const string FILENAME = @"c:\temp\test.xml";
static void Main(string[] args)
{
XDocument doc = XDocument.Load(FILENAME);
XElement[] models = doc.Descendants("model").ToArray();
for (int i = models.Count() - 1; i >= 0; i--)
{
if (models[i].Attributes().Count() == 0)
{
models[i].Remove();
}
}
}
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63286812
复制相关文章
相似问题
腾讯云开发者
