Hi
I'm trying to return all rows where the field DocumentXML equals 'Document'
The data in the field starts like this. <Document><Data><HTML> The field has other XML data that starts with <?mso-infoPathSolution name="urn:schemas-microsoft-com: but I don't want to see those rows. I only want to see rows with <Document> in the DocumentXML field. I'm getting the following error. Sorry my SQL is not strong.
Msg 402, Level 16, State 1, Line 1
The data types xml and varchar are incompatible in the equal to operator.
SELECT [EnrollmentDocumentID]
,[EnrollmentID]
,[DocumentID]
,[DocumentCategoryID]
,[DocumentName]
,[DocumentBriefDescription]
,[DocumentLinkPath]
,[DocumentXML]
,[StaffID]
,[ProfileMPIID]
,[CreatedDate]
,[AdmissionID]
,[EnrollmentDocumentGroup]
,[ConsentMPIID]
FROM [Caseworks].[dbo].[EnrollmentDocument]
WHERE DocumentXML='Document'
Try changing your WHERE clause to this:
WHERE CAST(DocumentXML AS NVARCHAR(MAX)) LIKE '<Document>%'
January 12, 2024 at 4:25 pm
Thank you, that worked perfectly.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy