Contained Databases

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.

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