XML Parsing Issue

• hitejaspatel

  Grasshopper

  Points: 18

  More actions

  November 21, 2008 at 12:12 pm

  #94842

  I am looking something like this from XML file to table

  like attached text file for xml

  In database table I want to parse like

  COL1 COL2

  EmployeeID EID1564654

  EmployeeName ABC

  EmployeeTitle XYZ

  City NY

  How can I parse values from XML files ?:crazy:

• Glen

  SSCertifiable

  Points: 6727

  More actions

  November 21, 2008 at 12:54 pm

  #901853

  And the version of the SQL server is ...?

  And files are located where... (filesystem, stored in DB)?

• hitejaspatel

  Grasshopper

  Points: 18

  More actions

  November 21, 2008 at 7:16 pm

  #901947

  I attached file because I can't write XML code here. It wont show. XML code is passing as a XMLdocument.

  & SQL 2005/2008

  Thanks,

• Glen

  SSCertifiable

  Points: 6727

  More actions

  November 24, 2008 at 10:53 am

  #902632

  -- Import the file into an XML variable

  declare @xmlimportdata xml

  SELECT @xmlimportdata = xmldata

  FROM

  (

  SELECT *

  FROM OPENROWSET (BULK 'filename.xml' , SINGLE_BLOB) AS XMLDATA

  ) AS FileImport (XMLDATA)

  -- convert XML to table

  SELECT node.query('fn:local-name(.)') AS NodeName, node.query('./text()') AS NodeValue into #t

  FROM @xmlimportdata.nodes(N'//*') T(node);

  select * from #t

  drop table #t