How to quickly sort and select data from 10000+ table rows in C#?

Question

C++ / C#

The data is stored in an XML file with more than 10,000 rows of data. How to quickly select the necessary data?

Answer 1

Linq+Xml

Answer 2

Another option at the MS SQL level is to parse the file, make the necessary selection and transfer the data to the application.
Below I give an example of parsing an xml file into a table. Try to remake it for yourself
I took a piece from a working example where namespaces were used in a file. If you don't have them, delete them.

declare @xdat xml 

-- получаем xml-файл
SET @xdat  = (SELECT [DataXml]  FROM [TaskXml]  where TaskDocGUID='1A91AED3-B5DF-4526-81E9-D54C16BB82A7')

if object_id('TempDb..#AllDataTable') > 0 drop table #AllDataTable
SELECT Product.ref.value('declare namespace pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:Type/text())[1]', 'nvarchar(50)') AS [Type]
  ,Product.ref.value('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:FullName/text())[1]', 'nvarchar(255)') AS FullName
  ,Product.ref.value('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:ShortName/text())[1]', 'nvarchar(64)') AS ShortName
  ,Product.ref.query('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";   pref:Producer[1]') AS Producer
  ,Producer.ref.value('declare namespace  oref="http://fsrar.ru/WEGAIS/ClientRef";   (oref:ClientRegId/text())[1]', 'nvarchar(50)') AS ClientRegIdProducer
  ,Producer.ref.value('declare namespace  oref="http://fsrar.ru/WEGAIS/ClientRef";   (oref:INN/text())[1]', 'nvarchar(255)') AS INNProducer
INTO #AllDataTable
FROM @xdat.nodes('declare namespace rap="http://fsrar.ru/WEGAIS/ReplyAP";
          declare namespace ns="http://fsrar.ru/WEGAIS/WB_DOC_SINGLE_01";
          /ns:Documents/ns:Document/ns:ReplyAP/rap:Products/rap:Product') AS Product(ref)
CROSS APPLY Product.ref.nodes('declare namespace  pref="http://fsrar.ru/WEGAIS/ProductRef";  pref:Producer') AS Producer(ref)

 SELECT * FROM #AllDataTable

The code above is hard to read and understand. I'll try to give a better example

-- формируем докумет
DECLARE @xdat XML = (
    SELECT TOP 5 GUID
      ,StatusDoc
    FROM V_EGAIS.dbo.ActChargeOnHeader
    FOR XML path('List')
      ,ROOT('Document')
      ,ELEMENTS
      ,type
    )

-- ниже пример того что получилось
--  <Document>
--  <List>
--    <GUID>7F705894-441F-4B59-B6C4-0003579AE9BA</GUID>
--    <LDM>2016-02-04T15:58:01.650</LDM>
--  </List>
--  <List>
--    <GUID>D6EADAEB-AD45-4EB9-962D-0003B82CB431</GUID>
--    <LDM>2016-02-04T15:59:51.723</LDM>
--  </List>
--  <List>
--    <GUID>84BA1332-7C1F-4BAB-9923-0003D4B2CD7A</GUID>
--    <LDM>2016-02-04T15:58:57.443</LDM>
--  </List>
--  <List>
--    <GUID>A56EF787-3549-43DD-99E8-00043ED70C11</GUID>
--    <LDM>2016-02-04T15:56:59.223</LDM>
--  </List>
--  <List>
--    <GUID>16939553-7927-4221-B260-00044EBC225F</GUID>
--    <LDM>2016-02-04T15:57:29.297</LDM>
--  </List>
--</Document>
-- теперь производим выборку
SELECT Document.ref.value('(GUID/text())[1]', 'nvarchar(255)') AS [GUID]
       ,Document.ref.value('(StatusDoc/text())[1]', 'int') AS StatusDoc
FROM @xdat.nodes('/Document/List') AS Document(ref) -- тут мы показываем с какого раздела можно начинать брать данные.