XML Parsing: well formed check: undeclared entity

Question

I am using SSMS 2008 and I am getting the following error. Do you know what this means?

Msg 9448, Level 16, State 1, Line 4
XML parsing: line 1, character 89, well formed check: undeclared entity

Here was the code which caused this error:

DECLARE @t TABLE (j VARCHAR(MAX))
insert into @t
SELECT 
z.MyXml.value('.','NVARCHAR(MAX)') value
FROM  [evolv_cs].[dbo].[progress_note] 
CROSS APPLY (SELECT CAST([progress_note].[note_text] AS XML) [MyXml]) z

I also tried this query with same error results:

SELECT 
z.MyXml.value('.','NVARCHAR(MAX)') value
INTO #TEMP_PN
FROM  [evolv_cs].[dbo].[progress_note] 
CROSS APPLY (SELECT CAST([progress_note].[note_text] AS XML) [MyXml]) z

Here is some sample data before applying this XML statement:

<DIV class=gc-message-sms-row><SPAN class=gc-message-sms-from>TLS: </SPAN><SPAN class=gc-message-sms-text>Hi Reggie... I'm on my way to Lynn.. see you soon</SPAN> <SPAN class=gc-message-sms-time>3:09 PM </SPAN></DIV>
<P>List the intervention targeted from the Intermediary Goal: </P> <P>1. To review on call procedure with family.</P> <P>Interventions attempted during the session to target PCP goal(s):&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P> <P><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; FONT-SIZE: 9pt; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">Counselor attempted to call mother; however received an automated message stating this number is temporarily out of service.</SPAN>&nbsp;&nbsp; </P> <P>Effectiveness of Intervention: </P> <P>Not applicable as family was not available for session.</P> <P>Describe safety plan/PCP crisis plan review (reminder - any modification requires new signatures and safety plans need to be reviewed at a minimum monthly)</P> <P>Not applicable as family was not available for session.</P> <P>Therapeutic Homework Assignment (assignment should relate to intervention and be agreed upon by counselor and family):</P> <P>Not applicable as family was not available for session.</P>
This specialist spoke with Kayla in an attempt to schedule a session for this week.&nbsp; Kayla agreed to meet with this specialist on Friday at 10:30am.&nbsp; This specialist asked Kayla about the upcoming meeting with DCF and Kayla stated that it is about her coming transition, but did not want to discuss it further.&nbsp; Kayla asked if this specialist would meet with her on Monday as well, to take her downtown to go to a meeting at a modeling agency.&nbsp; This specialist stated that this specialist could meet with Kayla prior to that appointment, and then drop Kaya off at the appointment.&nbsp;
<P style="MARGIN: 0in 0in 0pt" class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt">Purpose of Contact:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN></B></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt">To coordinate with Structured Day Program Director and notify her that faxed has been sent to refer client to program for summer.<B><o:p></o:p></B></SPAN></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt"><o:p>&nbsp;</o:p></SPAN></B></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt">Description of Contact:<o:p></o:p></SPAN></B></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt"><o:p>&nbsp;</o:p></SPAN></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt">Counselor spoke with program director to verify she received referral form for client.<SPAN style="mso-spacerun: yes">&nbsp; </SPAN>Director confirmed that she received clients referral form but reported that due to the waiting list being so long that client would more than likely not be able to attend program for the summer, but would keep client on waiting list and notify counselor if a slot becomes available.<SPAN style="mso-spacerun: yes">&nbsp; </SPAN><SPAN style="mso-spacerun: yes">&nbsp;</SPAN><B><o:p></o:p></B></SPAN></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt"><o:p>&nbsp;</o:p></SPAN></B></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt">Relationship between contact description and treatment goals:<o:p></o:p></SPAN></B></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 9pt">An increase in communication between counselor and director will lead to a collaborative treatment approach and improvement in client's referral behaviors. </SPAN><SPAN style="FONT-FAMILY: 'Times New Roman','serif'; FONT-SIZE: 12pt"><o:p></o:p></SPAN></P> <P style="MARGIN: 0in 0in 0pt" class=MsoNormal><o:p><FONT size=3 face=Calibri>&nbsp;</FONT></o:p></P>
<P style="BACKGROUND: white"><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; FONT-SIZE: 10pt">List the intervention targeted from the Intermediary Goal: The mother will assess what is working well in her home to assist the client with decreasing her referral behaviors.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN></P> <P style="BACKGROUND: white"><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; FONT-SIZE: 10pt">Interventions attempted during the session to target PCP goal(s):&nbsp;&nbsp;The counselor met with the&nbsp;mother and the client in the home for a face to face session. The counselor noted that the client and the mother were working together to complete household chores. The counselor asked the family to assess if the time frames to complete the chores id effective for the family. The mother and the client reported that the time&nbsp;frames to complete chores are effective. The counselor asked the mother to identify&nbsp;what is effective to assist the client with maintaining her decreased negative behaviors. The mother reported that by educating the client on her expectations and allowing her to make the choice to comply is effective. The counselor and family role played communicating the expectations. The counselor noted that the mother will give the client a directive and allow her a time frame to complete the directive. The client stated that now that her mother leaves her alone once she tells her what to do allow her time to process what her mother wants her to do. The client reported that she does not get upset anymore because she can complete the task&nbsp;within the time frame.<o:p></o:p></SPAN></P> <P style="BACKGROUND: white"><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; FONT-SIZE: 10pt">The counselor and family reviewed personal safety and the court recommendations for the client's protective supervision. The client stated that she is&nbsp;aligned with the&nbsp;following through with the recommendations even while she is on her missions' trip.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <o:p></o:p></SPAN></P> <P style="BACKGROUND: white"><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; FONT-SIZE: 10pt">Effectiveness of Intervention: The intervention appeared to be effective as evidenced by the family's willingness to identify barriers that may prevent them from follow through with identified contingences in the home.<o:p></o:p></SPAN></P> <P style="BACKGROUND: white"><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; FONT-SIZE: 10pt">Describe safety plan/PCP crisis plan review (reminder - any modification requires new signatures and safety plans need to be reviewed at a minimum monthly) The&nbsp;family and counselor reviewed to client's safety plan. No barriers or breakdowns were identified at this time.&nbsp;<o:p></o:p></SPAN></P> <P style="BACKGROUND: white"><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; FONT-SIZE: 10pt">Therapeutic Homework Assignment (assignment should relate to intervention and be agreed upon by counselor and family): The mother was asked to review the safety plan with identified supports. The client as asked to follow through with obtaining contact numbers for her new peers from church.<o:p></o:p></SPAN></P> <P style="MARGIN: 0in 0in 10pt" class=MsoNormal><o:p><FONT size=3 face=Calibri>&nbsp;</FONT></o:p></P>

I finished going through the tutorials Ahmed referenced, but I didn't yet find how to incorporate the HTML DTD into my SQL code. I see how this might work in an HTML file of course.

So instead I tried replacing all " " occurrences with " " via TSQL, but this gave me a different error: "XML parsing: line 2, character 127, A string literal was expected". I would still prefer to use the HTML DTD however. How do I do that with the tsql code above?

What is line 2 referring to? If it is referring to this sample code then it does not make sense to me. After replacing all   instances in the sample data above, I looked at line 2 which is the same as line 2 of this sample data. And character 127 = "v" from "interventions"

Answer 1

I think the entity reference   in your XML data is not ok because the system can't find the entity named nbsp. This typically comes from HTML, not bare XML. Thus you have to use the HTML DTDs in your DTDs or define that entity manually just like it is done in HTML or simply filter the data textually before doing XML processing on it.

If you use DTD, you could simply write in it:

<!ENTITY nbsp "&#160;">

If you do not use them, you simply replace all entity references   by character references