create a query that fetch data as XML more faster with filters

i’m using SQL server 2014, trying to get xml from SQL server in an hierarchical structure, `

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)

`

and i got outPut as

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

this query too 2s, but there is a lot of nodes without attributes

so i tried set @data.modify('delete //model[empty(@ModelId)]') to remove these nodes this takes nearly 30s. can anyone suggest a faster and better way to fetch xmal faster

Answer

Use 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:temptest.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();
                }
            }

 
        }
    }
 
}