Further issues are apparent when using contained databases with OPENXML and getting errors along the line of Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and "Latin1_General_CI_AS" in the equal to operation.
Code Block |
---|
language | sql |
---|
title | OPENXML Sample |
---|
|
-- THE FOLLOWING CODE GETS COLLATION ERRORS IN A CONTAINED DATABASE
SELECT
*
INTO #xmlparse
FROM OPENXML(@hDoc, '/InputXML/', 1) x
-- THIS IS THE WAY YOU NEED TO WRITE IT INSTEAD (note I have only included the fields we actually use)
SELECT
x.id,
x.parentid,
x.nodetype,
localname = x.localname COLLATE DATABASE_DEFAULT,
text = x.text COLLATE DATABASE_DEFAULT
INTO #xmlparse
FROM OPENXML(@hDoc, '/InputXML/', 1) x |