SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with openxml


Problem with openxml

Author
Message
S-322532
S-322532
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 268
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 Smile
Attachments
printrequestrows.txt (16 views, 1.00 KB)
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 1721
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



 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search