OpenXML not returning desired results

Hi all,

I have a SQL job where I do the following -

I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() -

DECLARE @.intDocINT
DECLARE @.xmlDocVARCHAR(8000)
IF(SELECTCOUNT(*)FROM DumpResults WHERE DumpResults.C1NOT IN (SELECT CleanTable.C1FROM CleanTable)) > 0
BEGIN
SET @.xmlDoc = (SELECT *FROM DumpResultsWHERE DumpResults.C1NOT IN (SELECT CleanTable.C1FROM CleanTable)FOR XML RAW)
SET @.xmlDoc ='<TABLE>' + @.xmlDoc +'</TABLE>'PRINT @.xmlDoc

EXECsp_xml_preparedocument @.intDocOUTPUT, @.xmlDoc
--INSERT INTOCleanTable(C1, C2, C3, C4, C5)SELECTC1, C2, C3, C4, C5, C6FROM OPENXML(@.intDoc,'/row',1)
WITH (C1 INT,C2CHAR(3),C3CHAR(3) ,C4FLOAT,C5INT)

EXECsp_xml_removedocument @.intDocENDELSE

Output that I get is -

<TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE
(0 row(s) affected)

SO "PRINT @.xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply

If anyone has any better method to do it, inputs are welcome.

Thanks

The issue is with your xpath.

Try it like this FROM OPENXML(@.intDoc, '/Table/row', 1)

Everything else looks to be right. Your XML has attributes and your setting your OPENXML to be with attribute centric. Just your XPATH is telling it to look at the root level for the row element, but its not at the root element.

|||

Hey guess what I had tried '/Table/row' earlier too.. But didn't work. Now once you said I looked at my query, and the place where I am appending -

SET @.xmlDoc = '<Table>' + @.xmlDoc + '</Table>'

I changed the case of "Table" and tried.. and it worked.. didn't know it was case sensitive..

Thanks

|||

Thats great,

Yes XML is very case sensitive :P