Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RE: - XML import Solution-Encoding="UTF-8" Expand / Collapse
Author
Message
Posted Thursday, October 09, 2008 4:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, Visits: 307
--==== 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





  Post Attachments 
consolidatedlist.zip (5 views, 60.21 KB)
Post #583184
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse