Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
doshim
Starting Member
1 Post |
 Posted - 2009-12-28 : 15:34:32
|
| Hi,I am having one xml file mentioned below and the data from this xml file needs to be exported to SQL Server 2005 table with the help of Stored Procedure. The SQL statement used in the Stored Procedure is mentioned below it. The problem I am facing is that the SELECT statement returns NULL values for all the 3 columns. And the restriction is that I cannot make any changes to the XML file. XML FILE=========<?xml version="1.0" ?><DeltaPublish xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/DeltaPublish.VERSION_1"><HRData><PublishDate class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/HRData.VERSION_1"><ProcessDate>2009-11-03</ProcessDate><Employee class="R"><EmployeeID>1</EmployeeID><LastName>xyz</LastName><FirstName>abc</FirstName></Employee><Employee class="R"><EmployeeID>2</EmployeeID><LastName>pqr</LastName><FirstName>def</FirstName></Employee></PublishDate></HRData></DeltaPublish>SQL STATEMENT================create TABLE #WorkingTable (Data XML) INSERT INTO #WorkingTable SELECT * FROM OPENROWSET(BULK '<xmlfilename>',SINGLE_BLOB) AS data DECLARE @XML AS XML, @hDoc AS INT SELECT @XML = Data FROM #WorkingTable EXEC sp_xml_preparedocument @hDoc OUTPUT , @XML , '<DeltaPublish xmlns:myns="http://xmlns.oracle.com/Enterprise/Tools/schemas/DeltaPublish.Version_1" />'SELECT EmployeeID, LastName, FirstNameFROM OPENXML(@hDoc, '/myns:DeltaPublish/myns:HRData/myns:PublishDate/myns:Employee/',2)WITH (EmployeeID NUMERIC 'myns:EmployeeID',LastName VARCHAR(50) 'myns:LastName',FirstName VARCHAR(50) 'myns:FirstName')RegardsMehul DoshiNew Jersey, USA |
|
|
sql-programmers
Posting Yak Master
190 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
XML with two namespaces