August 1, 2008 at 8:23 am
1. I am not a developer, I have extensive DBA background in "another" relational db.
2. I took it upon myself to figure out how to get XML files into my SS 2005 db.:w00t:
3. I am not now, nor do I plan on, using any code other than that in the database (stored procedures).
Whew, glad I got that out of the way.
I have tried the two following solutions for getting xml data from a file on my Windows OS to the SS 2005 db. I want either of these solutions to work, because I _almost_ understand either of these and don't want to have to toss this work aside without explanation of why it would never work.
(almost)Solution 1 (that currently doesn't work):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[POXML](
@strXML VARCHAR(2000)
)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO PO(PO_ID, PO_DATE, CUST_PO_NBR, po_rcvd_dttm)
SELECT '1', podate, ponumber, '07-29-2008'
FROM OPENXML(@XMLDocPointer,'/tset',2)
WITH (podate VARCHAR(30) '@po_date', ponumber varchar(30) '@cust_po_nbr')
COMMIT
EXEC sp_xml_removedocument @XMLDocPointer
RETURN
USE [testDB]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[POXML]
@strXML = N'c:\temp\TEST_850.XML'
SELECT'Return Value' = @return_value
GO
As you Guru's will point out, I can't pass a file name where actual data is required in the strXML variables, or I will get a "Invalid at the top level of the document." message. So, can I alter the above to be able to pass the file name? Or, can I set the @strXML variable to a full select statement where the results is the value of an xml type column (which I have successfully loaded with the xml data from the file)? Please include a specific code sample integrated with the above for either Q. BTW, I have created an XML schema collection as I guess a sort of "template" for the file of xml data I want to load/parse. I'm not really sure what to do with this or how to use it, I think maybe I need to associate it somehow with the file or the relational table column where I loaded my xml raw data...
(almost) Solution #2: I have loaded the raw xml data into a relational table column (using the working code below), now I want to parse the raw xml data into relational table columns (this next code doesn't work...).
GO
INSERT INTO MYXMLDATA_raw (mxd_id, mxd_date, mxd_transmission)
SELECT 1, '07-29-2008', xmlData FROM
(
SELECT * FROM OPENROWSET
(BULK 'C:\TEMP\TEST_850.xml',SINGLE_CLOB)
AS xmlData
) AS feed (xmlData)
GO
SELECT * FROM MYXMLDATA_raw
CREATE PROCEDURE dbo.POXML(
@strXML VARCHAR(2000)
)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO PO(PO_ID, PO_DATE, ORIGINATOR_ID,VET_ID,CUST_PO_NBR, po_rcvd_dttm)
SELECT '1', podate, '1','1', ponumber, '07-29-2008'
FROM OPENXML(@XMLDocPointer,'/tset',2)
WITH (podate VARCHAR(30) '@po_date', ponumber varchar(30) '@cust_po_nbr')
COMMIT
EXEC sp_xml_removedocument @XMLDocPointer
RETURN
CREATE PROCEDURE dbo.TestOpenXML(
@strXML VARCHAR(2000)
)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO Employees(EmployeeName, EmployeeSalary, DeptID)
SELECT NAME, SALARY, DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (NAME VARCHAR(30) ‘@NAME’, SALARY INT ‘@SALARY’, DEPTID INT '@DEPTID')
COMMIT
EXEC sp_xml_removedocument @XMLDocPointer
RETURN
(one attempt at using XQuery...where do I put the table names????)
declare @ponumber int
declare @XmlHandle int
SELECT @ponumber=cust_po_nbr
FROM OPENXML (@XmlHandle, '/tset/',1)
WITH (cust_po_nbr int '../@id')
print cast(@ponumber as varchar(50))
doesn't compile clean, if handle 0 is XmlHandle, can I set/how to in declare? here's the error:
Msg 8179, Level 16, State 5, Line 4
Could not find prepared statement with handle 0.
(yet another attempt, a very straightforward one to me, but ...)
INSERT INTO PO (PO_ID, PO_DATE, CUST_PO_NBR, po_rcvd_dttm)
SELECT '1', MYXMLDATA_raw.mxd_transmission.('@podate', 'DATETIME'),
MYXMLDATA_raw.mxd_transmission.('@ponumber', 'VARCHAR'), '07/29/2008'
FROM MYXMLDATA_raw
COMMIT
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Yes, the file contains valid XML data, levels, etc, and the first line starts with
?xml version="1.0"?
tset name = "850"
ponumber>0002222</ponumber
podate>07/29/2008</podate
/tset
2 relational tables, one called MYXMLDATA_raw with an id column and an xml datatype col; second table called PO with po_id, cust_po_nbr, po_date
Sorry to be so verbose, but I wanted to ensure gurus that I _have_ done reading and additional due diligence on this, with no solution and the clock ticking...
Thanks in advance for your expertise and willingness to share time and solutions.
Sign me...loves to learn, hates to code!
August 1, 2008 at 9:40 am
I would suggest using BULK INSERT. Check it out in BOL (Books Online), especially the XML section.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 4, 2008 at 7:42 am
Thank you very much for your suggestion, I will check that out.
However, I still have the invalid code I originally posted and would really like to know where I have coded incorrectly or ??. Gurus?
August 4, 2008 at 8:48 am
OK, sure.
First case:
jmclain (8/1/2008)
(almost)Solution 1 (that currently doesn't work):...
CREATE PROCEDURE [dbo].[POXML](
@strXML VARCHAR(2000)
)
...
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO PO(PO_ID, PO_DATE, CUST_PO_NBR, po_rcvd_dttm)
SELECT '1', podate, ponumber, '07-29-2008'
FROM OPENXML(@XMLDocPointer,'/tset',2)
WITH (podate VARCHAR(30) '@po_date', ponumber varchar(30) '@cust_po_nbr')
...
EXEC@return_value = [dbo].[POXML]
@strXML = N'c:\temp\TEST_850.XML'
...
As you Guru's will point out, I can't pass a file name where actual data is required in the strXML variables, or I will get a "Invalid at the top level of the document." message.
Yes, exactly. So you have already ansered this one your self.
So, can I alter the above to be able to pass the file name?
No. Normal SQL statmenets will not mess around with the filesystem, you need to use commands/facilities explicitly for that to get a file into SQL server and then use regular SQL commands to manipulate the data. You actually have figured this out is your later examples, so I am not sure why this example is here.
(almost) Solution #2: I have loaded the raw xml data into a relational table column (using the working code below), now I want to parse the raw xml data into relational table columns (this next code doesn't work...).
Unfortunately, you never tell us what the error is, or where it occurs in these 2 procedures and the SQL statements. Without that, we really can't tell you much.
(one attempt at using XQuery...where do I put the table names????)
...
SELECT @ponumber=cust_po_nbr
FROM OPENXML (@XmlHandle, '/tset/',1)
WITH (cust_po_nbr int '../@id')
...
doesn't compile clean, if handle 0 is XmlHandle, can I set/how to in declare? here's the error:
Msg 8179, Level 16, State 5, Line 4
Could not find prepared statement with handle 0.
Yes. OPENXML requires the sp_PrepareDocument to set the handle (and sp_RemoveDocument to clean it up). You actually have this in the other procs, so I do not understand why it isn't here.
(yet another attempt, a very straightforward one to me, but ...)
INSERT INTO PO (PO_ID, PO_DATE, CUST_PO_NBR, po_rcvd_dttm)
SELECT '1', MYXMLDATA_raw.mxd_transmission.('@podate', 'DATETIME'),
MYXMLDATA_raw.mxd_transmission.('@ponumber', 'VARCHAR'), '07/29/2008'
FROM MYXMLDATA_raw
COMMIT
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Yes. the expression "mxd_transmission.(..." is invalid. IF you are trying to use the XML.functions then you have to name one of them, like "mxd_transmission.query(...". There are 4 of them and you should check BOL for their exact syntax.
Sign me...loves to learn, hates to code!
Honestly, in cases like this, I do not believe there is any difference between the two.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply