Import XML in SQL Server table

Transact-SQL
1

I am trying to import this XML in a SQL table but keeps on getting NULL. Here's my code. Did I miss anything? I have been working on this for 3hrs. Please help!
Thanks.

<appt_links>
  <appt_link>
    <Field name="Resource ID">38</Field>
    <Field name="FromActivityID">4545</Field>
    <Field name="FromActivityDate">2017-12-08</Field>
    <Field name="FromActivityRouteID">663</Field>
    <Field name="ToActivityID">4546</Field>
    <Field name="ToActivityDate">2017-12-08</Field>
    <Field name="ToActivityRouteID">663</Field>
    <Field name="Link Type" />
  </appt_link>
</appt_links>

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM dwstage.[dbo].[OFSXMLFiles]
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT
[ResourceID],
[FromActivityID] ,
[FromActivityDate] ,
[FromActivityRouteID] ,
[ToActivityID] ,
[ToActivityDate],
[ToActivityRouteID]

FROM OPENXML(@hDoc, 'appt_links/appt_link',1)
WITH
(
[ResourceID] nvarchar '@ResourceID',
[FromActivityID] nvarchar '@FromActivityID',
[FromActivityDate] [datetime] '@FromActivityDate',
[FromActivityRouteID] nvarchar '@FromActivityRouteID' ,
[ToActivityID] nvarchar '@ToActivityID',
[ToActivityDate] [datetime] '@ToActivityDate',
[ToActivityRouteID] nvarchar 'ToActivityRouteID'
)
EXEC sp_xml_removedocument @hDoc

2

Your syntax in the WITH section is not quite right. Here is an example of how it should be:

SELECT
	ResourceID,
	FromActivityID
FROM OPENXML(@hDoc, 'appt_links/appt_link',1)
WITH
(
	 ResourceID nvarchar(32)  'Field[@name="ResourceID"]' ,
	 FromActivityID nvarchar(32)  'Field[@name="FromActivityID"]' 
)
EXEC sp_xml_removedocument @hDoc
3

thank you JamesK. It worked!