创建一个查询，使用过滤器更快地将数据提取为xml

eblbsuwk 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(349)

我使用的是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)

`
我得到的结果是

<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>

这个查询太复杂了，但是有很多节点没有属性
所以我试过了 set @data.modify('delete //model[empty(@ModelId)]') 删除这些节点需要将近30秒。有人能建议一个更快更好的方法来更快地获取xmal吗

sql sql-server xml c#sql-server-2014

来源：https://stackoverflow.com/questions/63286812/create-a-query-that-fetch-data-as-xml-more-faster-with-filters

1条答案

按热度按时间

vngu2lb81#

使用xml linq:

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();
                }
            }

        }
    }

}

赞(0）回复(0）举报 2021-07-24

我来回答

创建一个查询，使用过滤器更快地将数据提取为xml

1条答案

相关问题

热门标签

最新问答