Hi,

I am new to XML file to SQL table update.  I use sp_xml_preparedocument and openxml to extract the fields from xml file but it returns NULL highlight below. Could someone tell me what is wrong? I also notice both sp_xml_preparedocument and sp_xml_removedocument store procedures are not in SQL system store procedure.  Is this a problem?

Much appreciated.

Hi Brian_Ho,

In my test, the OPENXML works, you just missed to assign a namespaces parameter to the sp_xml_preparedocument. When namespaces are defined in a XML, things get a little complicated. Please see below sample.

DECLARE @x XML ='<NewHires>
<NewHire xmlns="http://ns.hr-xml.org/2007-04-15">
	<EmployeeInfo>
		<PersonName>
			<GivenName>Hot</GivenName>
			<MiddleName />
			<FamilyName>Buns</FamilyName>
		</PersonName>
	</EmployeeInfo>
</NewHire>
</NewHires>'

DECLARE @hdoc INT

EXEC sp_xml_preparedocument @hdoc OUTPUT,@x,'<a:NewHire xmlns:a="http://ns.hr-xml.org/2007-04-15"/>'

SELECT * FROM OPENXML(@hdoc,'/NewHires/a:NewHire/a:EmployeeInfo/a:PersonName',2)
WITH
(
GivenName varchar(100) 'a:GivenName',
FamilyName varchar(100) 'a:FamilyName'
)

exec sp_xml_removedocument  @hdoc
 

I've noticed that you specified a GiveName which is not an invalid column name in the result returned by OPENXML(in your XML it should be GivenName), it would return null if the column name is not correctly refered. Howerer in this case you got NULLs not for specying wrong column name. By the way, XML is case sensitive, a column name not specified in a correct case within the "WITH" will also return a NULL.

If you have any question, feel free to let me

Hi Eric,

My xml file has multiple namespaces like below. 

<NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="http://www.kronos.com/wta">

The xml file namespace is:

<?xml version="1.0" encoding="utf-8"?>
<NewHires batchId="8569021" batchDate="4/1/2015 6:34:16 PM" recordCount="4">
<NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="http://www.kronos.com/wta">
<TypeOfHire>
 <StandardValue>NewHire</StandardValue>
</TypeOfHire>
<EmployeeInfo>
 <PersonName>
  <GivenName>Hot</GivenName>
  <MiddleName />
  <FamilyName>Buns</FamilyName>
  </PersonName>

.....

I modified my query based on your recommendation, I received below error. 

DECLARE @x XML
SELECT @x=N
FROM OPENROWSET (BULK 'D:\Temp\Bad.Test.xml', SINGLE_BLOB) as NewHire (N)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x, '<a:NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="'">http://www.kronos.com/wta">'

SELECT *
FROM OPENXML (@hdoc, '/NewHires/a:NewHire/a:EmployeeInfo/a:PersonName', 2)
WITH
(
 BatchDate datetime '../../../@batchDate',
 BatchId int '../../../@batchId',
 GivenName varchar(30) 'a:GivenName',
 FamilyName varchar(30) 'a:FamilyName'

)

EXEC sp_xml_removedocument @hdoc

Here is the error:
(1 row(s) affected)
The XML parse error 0xc00ce01d occurred on line number 1, near the XML text "<a:NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="http://www.kronos.com/wta">".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Reference to undeclared namespace prefix: 'a'.'.
Msg 8179, Level 16, State 5, Line 12
Could not find prepared statement with handle 0.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

How do I define the tag for multiple namespsaces?

Thanks again!

Hi Eric,

Please ignore my previous email.  I missed a "/" at the end of the a tag. 

Thanks again.  

Brian.

Hi Eric,

Could you share some documentation to describe using "a" tag for namespace on sp_xml_preparedocument and OpenXML?  I would like to learn more about it.

Thanks again!

Brian.

Hi Brian_Ho,

It is not a specific "a" tag, you could use "b","c" or else instead.

In you case default namespace is specified as "xmlns="http://ns.hr-xml.org/2007-04-15"", I just have no idea on if there's a way to access the elements or attribute for a default namespace, I tried to specify the default namespace and sadly it does not work.

EXEC sp_xml_preparedocument @hdoc OUTPUT,@x,'<NewHire xmlns="http://ns.hr-xml.org/2007-04-15"/>'

SELECT * FROM OPENXML(@hdoc,'/NewHires/NewHire/EmployeeInfo/PersonName',2)

So I just tried to set the default namespace a prefix and it works. To have a better understanding on namespace, you can refer to Understanding XML Namespace.

By the way, if you're interested in how to access the elements and attributes via a default spacename, you can try the XQUERY, see more from here.

If you have any question, feel free to let me know.