Блог

У меня есть XML-документ, который мне нужно перенести в таблицу SQL Server. Все работает нормально, но не уверен, почему я получаю нулевые значения для параметра; в моем случае это instancekey

XML-документ

 <response>
- <item instancekey="0">
  <title>First Steps in Photo Shop and Digital Imaging</title> 
  <field_unit_instance_code>VE1EC37</field_unit_instance_code> 
  <field_market_area_description>Adults</field_market_area_description> 
  <field_ssa_description>Creative Arts, Design and Crafts</field_ssa_description> 
  <field_school_owning>Creative Arts and Digital Industries</field_school_owning> 
  <field_course_overview /> 
  <field_entry_requirements /> 
  <field_teaching_methods /> 
  <field_modules_and_assessment /> 
  <field_career_options_and_progres /> 
  <field_equipment_needed_and_costs /> 
  <field_work_placement_field_trips /> 
</item>
- <item instancekey="1">
 <title>Print Techniques</title> 
 <field_unit_instance_code>VE1EC36</field_unit_instance_code> 
 <field_market_area_description>Adults</field_market_area_description> 
 <field_ssa_description>Creative Arts, Design and Crafts</field_ssa_description> 
 <field_school_owning>Creative Arts and Digital Industries</field_school_owning> 
 <field_course_overview /> 
 <field_entry_requirements /> 
 <field_teaching_methods /> 
 <field_modules_and_assessment /> 
 <field_career_options_and_progres /> 
 <field_equipment_needed_and_costs /> 
 <field_work_placement_field_trips /> 
</item>
//remain records
  

Скрипт SQL Server для импорта XML-данных

  DECLARE @x xml
 SELECT @x=P
 FROM OPENROWSET (BULK 'H:My Workinstance_marketing.xml', SINGLE_BLOB ) AS  COURSE_INSTANCE(P)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT *
FROM OPENXML(@hdoc,'/response/item', 1)
WITH(
    instancekey int
   )    

 SELECT *
  FROM OPENXML(@hdoc,'/response/item', 2)
  WITH(
     CourseInstanceKey int '../../@instancekey',  //Getting Null Value here
     title varchar(300),
     field_unit_instance_code varchar(300),
     field_market_area_description varchar(3000),
     field_ssa_description varchar(3000),
     field_school_owning varchar(3000),
     field_course_overview varchar(3000), 
     field_entry_requirements varchar(3000),
     field_teaching_methods varchar(3000),
     field_modules_and_assessment varchar(3000),
     field_career_options_and_progres varchar(3000),
     field_equipment_needed_and_costs varchar(3000),
     field_work_placement_field_trips varchar(3000)
    )   

       EXEC sp_xml_removedocument @hdoc
  

Использование FROM OPENXML с окружающими вызовами sp_xml_preparedocument и sp_xml_removedocument устарело и должно быть заменено современными методами XML .nodes() , .query() , .value() и .modify() .

Ваш запрос будет быстрее, чище и удобнее в обслуживании, если вы сформулируете его следующим образом:

 DECLARE @x XML=
N'<response>
  <item instancekey="0">
    <title>First Steps in Photo Shop and Digital Imaging</title>
    <field_unit_instance_code>VE1EC37</field_unit_instance_code>
    <field_market_area_description>Adults</field_market_area_description>
    <field_ssa_description>Creative Arts, Design and Crafts</field_ssa_description>
    <field_school_owning>Creative Arts and Digital Industries</field_school_owning>
    <field_course_overview />
    <field_entry_requirements />
    <field_teaching_methods />
    <field_modules_and_assessment />
    <field_career_options_and_progres />
    <field_equipment_needed_and_costs />
    <field_work_placement_field_trips />
  </item>
  <item instancekey="1">
    <title>Print Techniques</title>
    <field_unit_instance_code>VE1EC36</field_unit_instance_code>
    <field_market_area_description>Adults</field_market_area_description>
    <field_ssa_description>Creative Arts, Design and Crafts</field_ssa_description>
    <field_school_owning>Creative Arts and Digital Industries</field_school_owning>
    <field_course_overview />
    <field_entry_requirements />
    <field_teaching_methods />
    <field_modules_and_assessment />
    <field_career_options_and_progres />
    <field_equipment_needed_and_costs />
    <field_work_placement_field_trips />
  </item>
</response>';
  

—Запрос

 SELECT i.value('@instancekey','int') AS title
      ,i.value('title[1]','nvarchar(300)') AS title
      ,i.value('field_unit_instance_code[1]','nvarchar(300)') AS field_unit_instance_code
      ,i.value('field_market_area_description[1]','nvarchar(3000)') AS field_market_area_description
      ,i.value('field_ssa_description[1]','nvarchar(3000)') AS field_ssa_description
      ,i.value('field_school_owning[1]','nvarchar(3000)') AS field_school_owning
      ,i.value('field_course_overview[1]','nvarchar(3000)') AS field_course_overview
      ,i.value('field_entry_requirements[1]','nvarchar(3000)') AS field_entry_requirements
      ,i.value('field_teaching_methods[1]','nvarchar(3000)') AS field_teaching_methods
      ,i.value('field_modules_and_assessment[1]','nvarchar(3000)') AS field_modules_and_assessment
      ,i.value('field_career_options_and_progres[1]','nvarchar(3000)') AS field_career_options_and_progres
      ,i.value('field_equipment_needed_and_costs[1]','nvarchar(3000)') AS field_equipment_needed_and_costs
      ,i.value('field_work_placement_field_trips[1]','nvarchar(3000)') AS field_work_placement_field_trips
FROM @x.nodes('response/item') AS a(i)
  

 SELECT *
FROM OPENXML(@hdoc,'/response/item', 2)
WITH(
 CourseInstanceKey int '@instancekey',
 title varchar(300),
 field_unit_instance_code varchar(300),