February 27, 2013 at 8:59 pm
Hi,
I'm trying to read a column from the xml using openxml. Things are fine except while trying to read one column both by select statement and while using a CTE, i get column not found error.
Can anyone advise why i'm getting this error even though the column is present both in xml data and table definition.
I've attached the script for having an idea of the problem i'm facing.
thanks 🙂
February 27, 2013 at 11:19 pm
The immediate problem is having PKID set as an IDENTITY. Ditch that and it will run as is.
PKID INT IDENTITY(1,1) NOT NULL
Another thing that can cause a problem is the mapping flag for the OPENXML command. You don't specify a value so it defaults to zero and attribute-centric mapping. Fortunately, that is what you are trying to do. But it's better if you specify a value of 1 (a value of 2 is for element-centric mapping...enter a 2 and see what happens):
...
FROM OPENXML(@hdoc,'//row',1) WITH PrintRequestRows
Final suggestion is to use a TRY/CATCH block for sp_xml_preparedocument. I always declare and build my XML as a string first so I can use various functions for removing excess spaces, invalid characters, etc.
BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML
END TRY
BEGIN CATCH
BEGIN
IF @debug = 1
BEGIN
SELECT @strXML AS ModifiedXML
SELECT CONVERT(XML,@strXML) AS FormattedXML
END
SELECT
'Invalid XML' AS XMLStatus
RETURN
END
END CATCH
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply