January 22, 2014 at 12:46 pm
I have been trying to transfer data from an xml file (table) to a Sql server
Step 1 worked and was able to get the file into Sql server
Step 2 did not work as planned gives back null results (I have attached the xml file in the email)
Attached the xmlData file view in sql
also the .xml file
/*Step 1 get the xml file */
CREATE DATABASE Testxml
CREATE TABLE SourceXMLfiles
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO SourceXMLfiles (XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn, 2) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK N'D:\SQLBackup\Monroe\Tables\testxml.xml', SINGLE_BLOB) AS x;
SELECT * FROM SourceXMLfiles
/*Step 2*/
/*This step returns only null values..*/
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM SourceXMLfiles
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT
[AccPrimaryId]
,[AccountId]
--,[FacPrimaryId],[DeptName],[Address1],[Address2],[City],[State],[ZipCode],[Country],[DptPhone],[DptExtn],[Pager],[Contact],[ContPhone],[ContExtn],[ContPager],[FaxNumber],[LastUpdateTime],[LastUpdateUser],[CreatedTime],[CreatedUser],[ROMPrimaryId],[AcctType]
FROM OPENXML(@hDoc, '/resultset/row',2)
WITH
(
[AccPrimaryId] [bigint] '@AccPrimaryId',
[AccountId] [varchar](25)'AccountId'
--,[FacPrimaryId] [bigint],
--[DeptName] [varchar](25),
--[Address1] [varchar](25),
--[Address2] [varchar](25),
--[City] [varchar](15),
--[State] [varchar](3),
--[ZipCode] [varchar](10),
--[Country] [varchar](4),
--[DptPhone] [varchar](25),
--[DptExtn] [numeric](5, 0),
--[Pager] [varchar](25),
--[Contact] [varchar](25),
--[ContPhone] [varchar](25),
--[ContExtn] [numeric](5, 0),
--[ContPager] [varchar](25),
--[FaxNumber] [varchar](25),
--[LastUpdateTime] [datetime],
--[LastUpdateUser] [varchar](60),
--[CreatedTime] [datetime],
--[CreatedUser] [varchar](60),
--[ROMPrimaryId] [bigint],
--[AcctType] [tinyint]
)
EXEC sp_xml_removedocument @hDoc
GO
January 23, 2014 at 6:41 am
Ok..a friend of mine found the solution 🙂
Just replace this in the query
./column[@name=
SELECT *
FROM OPENXML(@hDoc, '/resultset/row',2)
WITH
(
AccPrimaryId varchar(30) './column[@name="AccPrimaryId"]',
AccountId varchar(30) './column[@name="AccountId"]'
--Etc.
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply