RE: - XML import Solution-Encoding="UTF-8"

• raym

  SSCrazy

  Points: 2213

  More actions

  October 9, 2008 at 4:36 am

  #194467

  --==== Command Syntax:

  DECLARE @STR VARCHAR(1000)

  SELECT @STR ='xml sel -T -t -m "/INDIVIDUALS/INDIVIDUAL" -v "position()" -o "," -v "FIRST_NAME[position()=1]" -o "," -v "@type" -o "," -c "SECOND_NAME" -n consolidatedlist.xml'

  EXEC (@Str)

  --==== Step 1: Prepare a Parameter Table

  --==== usp_ImportXML stored procedure looks for a temporary table called #tblXMLParams.

  --==== This temp table specifies the record-defining node, the delimiter to be used to

  IF NOT object_id('tempdb..#tblXMLParams') IS NULL

  BEGIN

  DROP Table #tblXMLParams

  END

  CREATE TABLE #tblXMLParams (XPathOrder int, NodeType VARCHAR(20), XPathExpression VARCHAR(500))

  INSERT INTO #tblXMLParams VALUES (0,'Record_Node','/INDIVIDUALS/INDIVIDUAL') ????????????

  This is what I have in the file xml file has

  so what will my XPathExpression be ?

  INSERT INTO #tblXMLParams VALUES (0,'Delimiter','|')

  INSERT INTO #tblXMLParams VALUES (1,'XPATH','position()')

  INSERT INTO #tblXMLParams VALUES (2,'node','FIRST_NAME[position()=1]')

  INSERT INTO #tblXMLParams VALUES (3,'Attribute','@type')

  INSERT INTO #tblXMLParams VALUES (4,'node','SECOND_NAME') ETC.....

  I want to extract the nodes as they are in the xml file then bulk insert into a staging table

  How do I do it with encoding="UTF-8"

  I HAVE ATTACHED THE XML FILE