SELECT values from XML column

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  July 17, 2008 at 4:31 am

  #71642

  Hi Friends,

  I am good in T-SQL but new to XML data type.

  I am creating a SSIS Package in which I need to extract data from XML columns and save the data in SQL Server Table (Destination)

  My Source Table Looks like:

  TableName: EmpInfo

  ID int IDENTITY,

  EmpFirstName varchar(50)

  EmpMiddleName varchar(50)

  EmpLastName varchar(50)

  Details XML

  here, Details feilds contains information like Local Address, Permanent Address, Phone No etc.

  My Destination Table Looks like:

  TableName: EmpInfo

  ID int IDENTITY,

  EmpFirstName varchar(50)

  EmpMiddleName varchar(50)

  EmpLastName varchar(50)

  LocalAddress varchar(50)

  PermanentAddress varchar(50)

  PhoneNo varchar(20)

  Now, I think you got my point. I need to extract the first four columns as it is but remaing three columns from XML columns.

  How I can do it in SSIS or even in T-SQL?

  Thanks in advance

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  July 22, 2008 at 1:27 am

  #846194

  Hi Hari,

  Is it possible to post your Details column xml structure???

  ---

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  July 22, 2008 at 1:46 am

  #846197

  USE [AdventureWorks]

  GO

  SELECT JobCandidateID,Resume FROM HumanResources.JobCandidate

  -- Consider Resume column

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers