Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with openxml Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 8:59 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 1:02 AM
Points: 141, Visits: 259
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 :)


  Post Attachments 
printrequestrows.txt (7 views, 1.38 KB)
Post #1424872
Posted Wednesday, February 27, 2013 11:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1424918
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse