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>%'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 12, 2024 at 4:25 pm
Thank you, that worked perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply