Why are NULL values being inserted with this SQLXML statement?

Why are NULL values being inserted with this SQLXML statement?

Post by Don » Sun, 26 Sep 2004 05:37:43


The following XML is supposed to be mapped and INSERTED into table Employees
with empid autonumber primary key field.
Query Analyzer says that three rows were affected, but all I get are new
rows with NULL values?
In addition to this issue, can I mix attribute and element mappings?

Thanks,

Don
----------------------------------------------------------------------------
----------------------------------

DECLARE @idoc int
DECLARE @doc varchar(2000)
SET @doc ='
<ArrayOfEmployee1 xmlns:xsd=" http://www.yqcomputer.com/ "
xmlns:xsi=" http://www.yqcomputer.com/ ">
<Employee1 empid="">
<firstname>aJoe</firstname>
<lastname>Johnson</lastname>
<hiredate>01/01/2004</hiredate>
<title>President</title>
</Employee1>
<Employee1 empid="">
<firstname>aBob</firstname>
<lastname>Smith</lastname>
<hiredate>02/01/2003</hiredate>
<title>Vice President</title>
</Employee1>
<Employee1 empid="">
<firstname>aJack</firstname>
<lastname>Hayes</lastname>
<hiredate>01/01/2000</hiredate>
<title>CFO</title>
</Employee1>
</ArrayOfEmployee1>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
/*
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
*/
INSERT INTO employees(lastname, firstname, title, hiredate)
SELECT lastname, firstname, title, hiredate
FROM OpenXML(@idoc,'ArrayOfEmployee1/Employee1')
WITH (lastname nvarchar(50) '/firstname',
firstname nvarchar(50) '/lastname',
title nvarchar(50) '/hiredate',
hiredate nvarchar(50) '/title')
 
 
 

Why are NULL values being inserted with this SQLXML statement?

Post by Adam Macha » Sun, 26 Sep 2004 05:47:26


You need to remove those foward slashes... change it to:

WITH (lastname nvarchar(50) 'firstname',
firstname nvarchar(50) 'lastname',
title nvarchar(50) 'hiredate',
hiredate nvarchar(50) 'title')