September 14, 2008 at 4:07 am
I have been battling with this xml file trying to load it into a sql table. I got an answer form the forum to try openxml so I can load it directly into a sql server 2005 table but I keep getting errors...
I copied and pasted sample data from the consolidatedlist.xml file into a variable and tried using openxml so i can understand how it works and this is the message I get?
Msg 8179, Level 16, State 5, Line 433
Could not find prepared statement with handle 0.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.
I can import the data into a table but still face the problem of splitting it....How can I load it directly into tbUnsecList from the xml file
CREATE TABLE tbLoadXml
(
xmlFileName VARCHAR(300) NOT NULL
,xml_data XML NOT NULL
)
GO
--==============================================================
--
--==============================================================
--==== Stage Data into xml table
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\FILES\consolidatedlist.xml'
---– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('INSERT INTO tbLoadXml(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
--SELECT * FROM tbLoadXml
--==============================================================
--
--==============================================================
--==== Create the table to hold the data
IF OBJECT_ID('tbUnsecList') IS NOT NULL
DROP TABLE tbUnsecList
GO
CREATE TABLE tbUnsecList
(
NAME1 VARCHAR(100)
,NAME2 VARCHAR(100)
,NAME3 VARCHAR(100)
,NAME4 VARCHAR(100)
,SURNAMEVARCHAR(100)
,DOBSTARTVARCHAR(100)
,DOBENDVARCHAR(100)
,NATIONALITYVARCHAR(100)
,POB VARCHAR(500)
,TITLE1VARCHAR(100)
,TITLE2VARCHAR(100)
,DESIGNATIONVARCHAR(500)
,ALIASGOODQUALITYAKAVARCHAR(100)
,ALIASLOWQUALITYAKAVARCHAR(100)
,PASSPORTNOVARCHAR(100)
,NATIONALIDENTIFICATIONVARCHAR(100)
,ADDRESSVARCHAR(100)
,LISTEDONVARCHAR(100)
,OTHERINFORMATION VARCHAR(500)
,EXTRACTDATEDATETIME
DEFAULT(GETDATE())
)
September 30, 2008 at 3:19 am
Please, explain the mapping between the XML nodes and the destination table columns.
Also take a look at this simple example:
declare@xxml
set@x = N'<persons>
<person>
<name>Phil</name>
<age>45</age>
</person>
<person>
<name>Jim</name>
<age>47</age>
</person>
<person>
<name>Verne</name>
<age>34</age>
</person>
</persons>'
selectPersons.Person.query('name').value('.', 'varchar(32)') as PersonName
,Persons.Person.query('age').value('.', 'int') as PersonAge
from@x.nodes
('
/persons/person
') Persons (Person)
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
September 30, 2008 at 7:13 am
Look in BOL for "OpenXML" (use the Index feature). This will give you a lot more information than just a post.
SQL Server requires you to prep a "memory space" for the XML document often called a document handle (BTW, if someone has a better plain vanilla explanation of this, please let me know). BOL will give you the procs you need in order to process that.
Granted, BOL is not 100% in describing XML stuff, but combining this with what you already know should get you a little further ahead than you currently are.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply