Unable to remove red underlines for XML import object in SQL Server Select

Question

I am importing an XML file into SQL Server 2019 using SSMS from a Windows 10 desktop. The script works but there are red underlines showing in the editor that make the code look like something is wrong.

I have tried closing SSMS and reopening but they are still there. I tried refreshing the intellisense from the main menu but still they are there.

This is the code:

Below is the code:

DROP TABLE IF EXISTS dbo.TechCourses;
GO

BEGIN TRANSACTION

CREATE TABLE dbo.TechCourses
(
    CourseName varchar(50) NOT NULL,
    CourseCode varchar(20) NOT NULL,
    StartDate datetime NOT NULL,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    StudentID int NOT NULL,
    Membership varchar(20) NOT NULL
)
GO

ALTER TABLE TechCourses
ADD CONSTRAINT TechCourses_PK PRIMARY KEY (StudentID, CourseName);
GO

COMMIT

INSERT INTO dbo.TechCourses (CourseName, CourseCode, StartDate, FirstName, LastName, StudentID, Membership)
    SELECT 
        XMLFromFile.Applicant.query('CourseName').value('.', 'VARCHAR(50)'),
        XMLFromFile.Applicant.query('CourseCode').value('.', 'VARCHAR(20)'),
        XMLFromFile.Applicant.query('StartDate').value('.', 'VARCHAR(50)'),
        XMLFromFile.Applicant.query('FirstName').value('.', 'VARCHAR(50)'),
        XMLFromFile.Applicant.query('LastName').value('.', 'VARCHAR(50)'),
        XMLFromFile.Applicant.query('StudentID').value('.', 'VARCHAR(16)'),
        XMLFromFile.Applicant.query('Membership').value('.', 'VARCHAR(20)')
    FROM 
        (SELECT CAST(XMLFromFile AS xml)
         FROM OPENROWSET(BULK 'C:\FilesForTesting\XmlLoadTest2.xml', SINGLE_BLOB) AS T(XMLFromFile)) AS T(XMLFromFile)
    CROSS APPLY 
        XMLFromFile.nodes('Root/Applicants/Applicant') AS XMLFromFile (Applicant);

The XMLFromFile object recognizes the Applicant sub object when I type a period after the object name. When I move the cursor under the red underline items the following message appears:

"Applicant" is not a valid function, property, or field.

The XML being imported is as follows:

<?xml version="1.0" encoding="utf-8"?>
<Root>
      <Applicants>
        <Applicant>
          <CourseName>Data Analytics 101 - Beginner"</CourseName>
          <CourseCode>DA101</CourseCode>
          <StartDate>2022-08-01 08:00:00</StartDate>
          <FirstName>Joe</FirstName>
          <LastName>Stewart</LastName>
          <StudentID>301</StudentID>
          <Membership>Gold</Membership>
        </Applicant>
        <Applicant>
          <CourseName>Data Analytics 101 - Beginner"</CourseName>
          <CourseCode>DA101</CourseCode>
          <StartDate>2022-08-01 08:00:00</StartDate>
          <FirstName>Sandy</FirstName>
          <LastName>Gomez</LastName>
          <StudentID>302</StudentID>
          <Membership>Bronze</Membership>
        </Applicant>
        <Applicant>
          <CourseName>Data Analytics 101 - Beginner"</CourseName>
          <CourseCode>DA101</CourseCode>
          <StartDate>2022-08-01 08:00:00</StartDate>
          <FirstName>Cindy</FirstName>
          <LastName>Yarnov</LastName>
          <StudentID>303</StudentID>
          <Membership>Silver</Membership>
        </Applicant>
        <Applicant>
          <CourseName>Data Analytics 101 - Beginner"</CourseName>
          <CourseCode>DA101</CourseCode>
          <StartDate>2022-08-01 08:00:00</StartDate>
          <FirstName>Olek</FirstName>
          <LastName>Kovalenko</LastName>
          <StudentID>304</StudentID>
          <Membership>Gold</Membership>
        </Applicant>
        <Applicant>
          <CourseName>Data Analytics 101 - Beginner"</CourseName>
          <CourseCode>DA101</CourseCode>
          <StartDate>2022-08-01 08:00:00</StartDate>
          <FirstName>Sergio</FirstName>
          <LastName>Lopez</LastName>
          <StudentID>305</StudentID>
          <Membership>Gold</Membership>
        </Applicant>
      </Applicants>
</Root>

Is there something not right in the code?

Answer 1

There is nothing syntactically wrong with your query, although SSMS may be getting confused due to the fact that you have a column and a derived table by the same name.

You might be able to un-confuse it by renaming one of them.

Further improvements include putting the conversion inside an APPLY, and using .value as it's supposed to be used (without unnecessary .query), and using correct data types.

INSERT INTO dbo.TechCourses 
(
    CourseName, CourseCode, StartDate, FirstName, LastName, StudentID, Membership
)
SELECT 
   x1.Applicant.value('(CourseName/text())[1]', 'VARCHAR(50)'),
   x1.Applicant.value('(CourseCode/text())[1]', 'VARCHAR(20)'),
   x1.Applicant.value('(StartDate/text())[1]', 'datetime'),
   x1.Applicant.value('(FirstName/text())[1]', 'VARCHAR(50)'),
   x1.Applicant.value('(LastName/text())[1]', 'VARCHAR(50)'),
   x1.Applicant.value('(StudentID/text())[1]', 'int'),
   x1.Applicant.value('(Membership/text())[1]', 'VARCHAR(20)')

FROM OPENROWSET(BULK 'C:\FilesForTesting\XmlLoadTest2.xml', SINGLE_BLOB) AS T1(BinaryData)
CROSS APPLY (VALUES (
    CAST(T1.BinaryData AS xml)
) ) AS T2(XMLFromFile)

CROSS APPLY T2.XMLFromFile.nodes('Root/Applicants/Applicant') AS x1(Applicant);

db<>fiddle