Inserting XML into SQL Server 2008 table

• User7766

  SSCommitted

  Points: 1969

  More actions

  June 27, 2011 at 3:59 am

  #242864

  Hi All,

  I'm having a class file with two unique identifiers as fields and another class file for adding these 2 fields to a generic LIST.

  I've constructed serialized object from those 2 classes and returned the XML string. Now i need to insert the XML into a table using CTE Joins, instead of using OPENXML.. Can anyone please let me know how to do this..

  BTW, my XML is like this:

  <SkillsetListDTO>

  <RefSkillsets>

  <RefSkillSetDepartmentsDTO>

  <Skillsetid>ec5b5ed6-6409-4fa5-ac0f-a2fe138a7c89</Skillsetid>

  <Departmentid>e392e0df-88d4-4ffa-9f7c-289cc2b3a860</Departmentid>

  </RefSkillSetDepartmentsDTO>

  <RefSkillSetDepartmentsDTO>

  <Skillsetid>9148b498-6fbd-42f3-af92-76b3a44ff574</Skillsetid>

  <Departmentid>e392e0df-88d4-4ffa-9f7c-289cc2b3a860</Departmentid>

  </RefSkillSetDepartmentsDTO>

  <RefSkillSetDepartmentsDTO>

  <Skillsetid>31eac6ee-eddd-45c9-854c-e8cc7cdaaa71</Skillsetid>

  <Departmentid>e392e0df-88d4-4ffa-9f7c-289cc2b3a860</Departmentid>

  </RefSkillSetDepartmentsDTO>

  </RefSkillsets>

  </SkillsetListDTO>';

  Awaiting your responses,

  Thanks,

  Pavan

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  June 27, 2011 at 4:51 am

  #1344394

  Use the XQuery .nodes method to shred the XML into a series of individual nodes, then the XQuery .value method on those nodes to extract values that can be manipulated with T-SQL:

  DECLARE @x xml

  SELECT @x = '<SkillsetListDTO>

  <RefSkillsets>

  <RefSkillSetDepartmentsDTO>

  <Skillsetid>ec5b5ed6-6409-4fa5-ac0f-a2fe138a7c89</Skillsetid>

  <Departmentid>e392e0df-88d4-4ffa-9f7c-289cc2b3a860</Departmentid>

  </RefSkillSetDepartmentsDTO>

  <RefSkillSetDepartmentsDTO>

  <Skillsetid>9148b498-6fbd-42f3-af92-76b3a44ff574</Skillsetid>

  <Departmentid>e392e0df-88d4-4ffa-9f7c-289cc2b3a860</Departmentid>

  </RefSkillSetDepartmentsDTO>

  <RefSkillSetDepartmentsDTO>

  <Skillsetid>31eac6ee-eddd-45c9-854c-e8cc7cdaaa71</Skillsetid>

  <Departmentid>e392e0df-88d4-4ffa-9f7c-289cc2b3a860</Departmentid>

  </RefSkillSetDepartmentsDTO>

  </RefSkillsets>

  </SkillsetListDTO>'

  -- INSERT YourTargetTableNameGoesHere(Skillsetid, Departmentid)

  SELECT Skills.query('.'),

  Skills.value('(./Skillsetid)[1]', 'uniqueidentifier') AS Skillsetid,

  Skills.value('(./Departmentid)[1]', 'uniqueidentifier') AS Departmentid

  FROM @x.nodes('SkillsetListDTO/RefSkillsets/RefSkillSetDepartmentsDTO') AS SkillsDepts(Skills)

  Eddie Wuerch
  MCM: SQL