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

Mikael Eriksson · Accepted Answer · 2014-10-31 13:49:57Z

2

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

answered Oct 31, 2014 at 13:49

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ann L. · Accepted Answer · 2014-10-31 11:29:27Z

0

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.

answered Oct 31, 2014 at 11:29

Ann L.

14k5 gold badges39 silver badges67 bronze badges

1 Comment

user1781272 Over a year ago

Well spotted. That was an error but this still doesn't solve the problem. I corrected it on the post. Thanks

Collectives™ on Stack Overflow

OpenXML Import XML file to SQL server table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related