how to inser this xml string to my table?

• tnt750

  Old Hand

  Points: 349

  More actions

  January 31, 2012 at 4:24 am

  #391436

  i want insert this xml string to table

  and is correct form this xml string for use in sql server 2008?

  please help me for use xml

  my table is: id int,name nvarchar(50),pay bigint,tash nvarchar(5)

  <TBL_FACTOR>

  <RD_FACTOR>

  <ID>1</ID>

  <NAME>ali</NAME>

  <PAY>25000</PAY>

  <TASH>bas</TASH>

  </RD_FACTOR>

  <RD_FACTOR>

  <ID>2</ID>

  <NAME>hadi</NAME>

  <PAY>236000</PAY>

  <TASH>bad</TASH>

  </RD_FACTOR>

  <RD_FACTOR>

  <ID>3</ID>

  <NAME>hamed</NAME>

  <PAY>164000</PAY>

  <TASH>bi</TASH>

  </RD_FACTOR>

  </TBL_FACTOR>

• Paul White

  SSC Guru

  Points: 150468

  More actions

  January 31, 2012 at 4:32 am

  #1440499

  DECLARE @xml xml =

  N'<?xml version="1.0" encoding="utf-16"?>

  <TBL_FACTOR>

  <RD_FACTOR>

  <ID>1</ID>

  <NAME>ali</NAME>

  <PAY>25000</PAY>

  <TASH>bas</TASH>

  </RD_FACTOR>

  <RD_FACTOR>

  <ID>2</ID>

  <NAME>hadi</NAME>

  <PAY>236000</PAY>

  <TASH>bad</TASH>

  </RD_FACTOR>

  <RD_FACTOR>

  <ID>3</ID>

  <NAME>hamed</NAME>

  <PAY>164000</PAY>

  <TASH>bi</TASH>

  </RD_FACTOR>

  </TBL_FACTOR>

  ';

  SELECT

  id = nodes.node.value('(./ID/text())[1]', 'integer'),

  name = nodes.node.value('(./NAME/text())[1]', 'nvarchar(50)'),

  pay = nodes.node.value('(./PAY/text())[1]', 'bigint'),

  tash = nodes.node.value('(./TASH/text())[1]', 'nvarchar(5)')

  FROM @xml.nodes('./TBL_FACTOR/RD_FACTOR') AS nodes (node);

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi