January 31, 2008 at 5:28 pm
I'm trying to pass multiple records into a stored procedure and insert them into a table. In the past I've done this very easily by using an XML data type for the input parameter. This time, I've run into a problem because one of the columns that I'm trying to insert is also an XML data type so the OpenXML function seems to be getting confused.
Here is the table that I'm trying to insert into:
CREATE TABLE tblDataSetValues(
DataSetRowID bigint IDENTITY(1,1) NOT NULL,
DataSetPersistID int NOT NULL,
RowType varchar(50) NOT NULL,
DateCreated datetime NOT NULL,
DateModified datetime NULL,
XMLValues xml NOT NULL,
CONSTRAINT pk_tblDataSetValues PRIMARY KEY CLUSTERED
(DataSetRowID ASC))
I'm trying to run the code below to insert two rows into this table
DECLARE@XMLValues xml
SET @XMLValues = '<a>
<i id="67890" rt="Wendy" dc="2007-01-02" x="<r><StringData><d><di><dk>Last Name</dk><dv>Something</dv></di></d><v>Schuman</v></StringData></r>"/>
<i id="67890" rt="Wendy" dc="2008-01-01" x="<r><StringData><d><di><dk>Last Name</dk><dv>Test</dv></di></d><v>New Value</v></StringData></r>"/>
</a>'
DECLARE @docHandle as int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XMLValues
INSERT INTO dbo.tblDataSetValues(DataSetPersistID, RowType, DateCreated, XMLValues)
SELECT id, rt, dc, x
FROM OPENXML (@docHandle, '/a/i',3)
WITH (id int, rt varchar(50), dc datetime, x xml)
EXEC sp_xml_removedocument @docHandle
When I execute the code, I receive the following error:
Msg 9415, Level 16, State 1, Line 2
XML parsing: line 2, character 45, well formed check: no '<' in attribute value
I've also tried changing the @XMLValues to be Element-centric because that was an error I received when I was using 1 for attribute centric and had an empty string for the X attribute.
DECLARE@XMLValues xml
SET @XMLValues = '<a>
<ds><id>67890</id><rt>Wendy</rt><dc>2007-01-02</dc><x><r><StringData><d><di><dk>Last Name</dk><dv>Something</dv></di></d><v>Schuman</v></StringData></r></x></ds>
<ds><id>67890</id><rt>Wendy</rt><dc>2008-01-01</dc><x><r><StringData><d><di><dk>Last Name</dk><dv>Test</dv></di></d><v>New Value</v></StringData></r></x></ds>
</a>'
DECLARE @docHandle as int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XMLValues
INSERT INTO dbo.tblDataSetValues(DataSetPersistID, RowType, DateCreated, XMLValues)
SELECT id, rt, dc, x
FROM OPENXML (@docHandle, '/a/ds',2)
WITH (id int, rt varchar(50), dc datetime, x xml)
EXEC sp_xml_removedocument @docHandle
This gives the following error:
Msg 6913, Level 16, State 1, Line 11
XML Validation: Declaration not found for element 'x'. Location: /*:x[1]
I know that the OpenXML function is getting confused by the XML values that I'm trying to insert, but I can't seem to figure out how to get around this problem. Does anyone have any suggestions how I can get this to work or another suggestion as to how I can pass multiple records to the stored procedure and insert them into the table?
Thanks for any help you can provide!
Wendy Schuman
February 1, 2008 at 12:38 pm
I figured out why this isn't working. The XMLValues column has an XML Schema associated with it. I can use the code below and insert the records into a different table and then move them into the tblDataSetValues table.
DECLARE@XMLValues xml
SET @XMLValues = '<a>
<ds><id>67890</id><rt>Wendy</rt><dc>2007-01-02</dc><x><r><StringData><d><di><dk>Last Name</dk><dv>Something</dv></di></d><v>Schuman</v></StringData></r></x></ds>
<ds><id>67890</id><rt>Wendy</rt><dc>2008-01-01</dc><x><r><StringData><d><di><dk>Last Name</dk><dv>Test</dv></di></d><v>New Value</v></StringData></r></x></ds>
</a>'
DECLARE @docHandle as int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XMLValues
INSERT INTO dbo.tblDataSetValues(DataSetPersistID, RowType, DateCreated, XMLValues)
SELECT id, rt, dc, x
FROM OPENXML (@docHandle, '/a/ds',2)
WITH (id int, rt varchar(50), dc datetime, x xml)
EXEC sp_xml_removedocument @docHandle
Wendy Schuman
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply