Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка строк из xml-файла  [new]
mosxe
Member

Откуда:
Сообщений: 2
Добрый день. Последние пару дней никак не могу решить одну ситуацию: Есть xml-файл
+
<education_plan >
  <name>test</name>
  <group_id>6575029309690231440</group_id>
  <compound_program_id>6496048125887279509</compound_program_id>
  <person_id>6584698427624525408</person_id>
  <person_fullname>Алексеев Кирилл Владимирович</person_fullname>
  <person_position_id>6584695855826419753</person_position_id>
  <person_position_name>Разработчик</person_position_name>
  <person_position_code>DA815ACF8A53D7E44ADCB938382458CA</person_position_code>
  <person_org_id>2423575132271016192</person_org_id>
  <person_org_name>MERLION</person_org_name>
  <person_subdivision_id>6040091575323209548</person_subdivision_id>
  <person_subdivision_name>Группа дистанционного обучения</person_subdivision_name>
  <person_subdivision_code>000198</person_subdivision_code>
  <person_code>45667</person_code>
  <create_date>2018-09-13T13:23:00Z</create_date>
  <finish_date>2018-09-13T13:23:00Z</finish_date>
  <plan_date>2018-09-13T13:23:00Z</plan_date>
  <state_id>0</state_id>
  <programs>
    <program>
      <id>5959071437000156305</id>
      <name>Тестовая учебная программа (не назначать)</name>
      <education_method_id>6575034627839454629</education_method_id>
      <type>education_method</type>
      <object_id>6576969200213636977</object_id>
      <object_name>Тестовая учебная программа (не назначать)</object_name>
      <object_start_date>2018-07-11T09:00:00Z</object_start_date>
      <create_date>2018-09-13T13:23:00Z</create_date>
      <plan_date>2018-09-20T00:00:00Z</plan_date>
      <result_type>event_result</result_type>
      <state_id>1</state_id>
      <readiness_percent>90</readiness_percent>
    </program>
    <program>
      <id>25928031</id>
      <name>Планирование, организация и контроль</name>
      <type>assessment</type>
      <object_id>6015396206078217355</object_id>
      <create_date>2018-09-13T13:23:00Z</create_date>
      <plan_date>2018-09-30T00:00:00Z</plan_date>
      <result_type>active_learning</result_type>
      <state_id>4</state_id>
      <readiness_percent>80</readiness_percent>
      <completed_parent_programs>
        <completed_parent_program>
          <program_id>5959071437000156305</program_id>
        </completed_parent_program>
      </completed_parent_programs>
    </program>
    <program>
      <id>6078856089697798669</id>
      <name>Координация работы сотрудников</name>
      <education_method_id>6078856089697798669</education_method_id>
      <type>education_method</type>
      <object_id>6405499774486846705</object_id>
      <object_name>Вебинар 'Координация работы сотрудников'</object_name>
      <object_code>CITILINK</object_code>
      <object_start_date>2017-06-16T11:00:23Z</object_start_date>
      <create_date>2018-09-13T13:23:00Z</create_date>
      <plan_date>2018-11-30T00:00:00Z</plan_date>
      <result_type>active_learning</result_type>
      <state_id>0</state_id>
    </program>
    <program>
      <id>21427088</id>
      <name>Координация работы сотрудников</name>
      <type>assessment</type>
      <object_id>6328243748511244692</object_id>
      <create_date>2018-09-13T13:23:00Z</create_date>
      <result_type>active_learning</result_type>
      <result_object_id>6605120038882207089</result_object_id>
      <state_id>0</state_id>
      <completed_parent_programs>
        <completed_parent_program>
          <program_id>5959071437000156305</program_id>
        </completed_parent_program>
        <completed_parent_program>
          <program_id>6078856661787743881</program_id>
        </completed_parent_program>
      </completed_parent_programs>
    </program>
    <program>
      <id>6078856661787743881</id>
      <name>Мотивация и развитие сотрудников</name>
      <education_method_id>6078856661787743881</education_method_id>
      <type>education_method</type>
      <object_name>Мотивация и развитие сотрудников</object_name>
      <object_start_date>2018-09-13T09:00:00Z</object_start_date>
      <create_date>2018-09-13T13:23:00Z</create_date>
      <result_type>active_learning</result_type>
      <state_id>0</state_id>
    </program>
</education_plan>

Нужно из этого файла выбрать "программы", которые имеют вложенную так скажем "программу" - <completed_parent_program>
Делается это довольно легко:
	SELECT 
		T2.Loc.query('.').value('(/program/id)[1]', 'nvarchar(100)') as id_event
		,T2.Loc.query('.').value('(/program/name)[1]', 'nvarchar(100)') as name_event
	FROM [WTDB].[dbo].education_plans as eds
	INNER JOIN [WTDB].[dbo].education_plan as edu
	CROSS APPLY data.nodes('/education_plan/programs/program') as T2(Loc)
		on  edu.id = eds.id 
	WHERE edu.id = 6600652548845478134 and T2.Loc.query('.').value('(/program/completed_parent_programs/completed_parent_program/program_id)[1]', 'nvarchar(100)') IS NOT NULL

Получилось две "программы", которые имеют вложенные "программы" - completed_parent_program.
И дальше мне нужно получить вот такую таблицу:
id name id_program name_programm state_id_program
25928031 Планирование организация и контроль 5959071437000156305 Тестовая учебная программа (не назначать) 1
21427088Координация работы сотрудников 5959071437000156305 Тестовая учебная программа (не назначать) 1
21427088Координация работы сотрудников 6078856661787743881 Мотивация и развитие сотрудников 0


Мне по идеи нужна какая-то рекурсия, чтобы я смог пройтись по родителям и вывести всех их детей. Если бы изначально были обычные таблицы, проблем бы не было, но т.к. все данные хранятся в xml-файле я не знаю как можно по ним пройтись и связать между ними.
Возможно кто-то хотя бы мысль подкинет в каком лучше направлении двигаться.
Заранее спасибо.

Сообщение было отредактировано: 1 окт 18, 14:31
1 окт 18, 13:39    [21691134]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк из xml-файла  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
select
 p.n.value('id[1]', 'nvarchar(100)'),
 p.n.value('name[1]', 'nvarchar(100)'),
 cpp.n.value('program_id[1]', 'nvarchar(100)')
from
[WTDB].[dbo].education_plans as eds
	INNER JOIN [WTDB].[dbo].education_plan as edu on edu.id = eds.id
	CROSS APPLY data.nodes('/education_plan/programs/program') p(n)
    cross apply p.n.nodes('completed_parent_programs/completed_parent_program') cpp(n)
where
 edu.id = 6600652548845478134
1 окт 18, 14:06    [21691209]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк из xml-файла  [new]
mosxe
Member

Откуда:
Сообщений: 2
invm,
Человеческое спасибо !!!
Делается все просто, но из-за не знания убил столько времени...
1 окт 18, 14:12    [21691238]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить