OpenXML Import XML file to SQL server table

Question

I am trying to set up an SQL job to import an XML file into an SQL Server table. Using OPENXML, I can't seem to select the specific data I need from the file. Here's my code and XML data. I am trying to select Facility and Entity_Code but when I run the code, these fields appear as blank.

I would like to transfer these fields into their own table.

Thanks in advance.

Declare @x xml

select @x=p 
from OPENROWSET(Bulk'\\vmirsdh01\fast_data\Small.xml', SINGLE_BLOB) as T(P)

Select @x

Declare @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

Select *
FROM OPENXML (@hdoc,'/Report/Tablix1/Details_Collection/Details',0)
with(Facility nvarchar(255) '@Facility',
Entity_Code nvarchar(255) '@Entity_Code')

exec sp_xml_removedocument @hdoc

'************ XML

<?xml version="1.0" encoding="utf-8"?><Report xsi:schemaLocation="T-Report https://csre.xxx.com%2FDevelopment%20Folder%2FIand%2FT-Report&amp;rs%3ACommand=Render&amp;rs%3AFormat=XML&amp;rs%3ASessionID=4keav12uayp33ve3uczpgmfr&amp;rc    %3ASchema=True" Name="T-Report" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="T_Report">
<Tablix1>
<Details_Collection><Details Facility="Fxx" Tool_Type="Base Build" Entity_Code="EquiP1" /></Details_Collection>
</Tablix1>
</Report>

Here is an executable version

Declare @x xml

select @x='<?xml version="1.0" encoding="utf-8"?><Report xsi:schemaLocation="T-Report https://csre.xxx.com%2FDevelopment%20Folder%2FIand%2FT-Report&amp;rs%3ACommand=Render&amp;rs%3AFormat=XML&amp;rs%3ASessionID=4keav12uayp33ve3uczpgmfr&amp;rc    %3ASchema=True" Name="T-Report" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="T_Report">
<Tablix1>
<Details_Collection><Details Facility="Fxx" Tool_Type="Base Build" Entity_Code="EquiP1" /></Details_Collection>
</Tablix1>
</Report>'

Declare @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

Select *
FROM OPENXML (@hdoc,'/Report/Tablix1/Details_Collection/Details',0)
with(Facility nvarchar(255) '@Facility',
Entity_Code nvarchar(255) '@Entity_Code')

exec sp_xml_removedocument @hdoc

Answer 1

You have a default namespace that you need to take into consideration xmlns="T_Report".

Using the XML variable directly your query would look like

with xmlnamespaces(default 'T_Report')
select D.X.value('@Facility', 'nvarchar(255)'),
       D.X.value('@Entity_Code', 'nvarchar(255)')
from @x.nodes('/Report/Tablix1/Details_Collection/Details') as D(X)

If you for some reason want to use openxml you need to declare the namespace in the third parameter to sp_xml_preparedocument.

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x, '<root xmlns:xx="T_Report"/>'

Select *
FROM OPENXML (@hdoc,'/xx:Report/xx:Tablix1/xx:Details_Collection/xx:Details',0)
with(Facility nvarchar(255) '@Facility',
Entity_Code nvarchar(255) '@Entity_Code')

exec sp_xml_removedocument @hdoc

Answer 2

Your XML has an opening tag of <Report> but your query is for an opening tag called <Result>.

While I can't swear that everything will work after you fix that (I don't do much with OPENXML) I'm fairly confident that that is a problem.