OPENXML doesn''t open anything

Question

Post reply

OPENXML doesn''t open anything

Dave-3000

SSCertifiable

Points: 6186
More actions
November 21, 2005 at 5:46 pm

#166352

I am trying to insert a row from an XML document into a table. Here is the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
---------------------------------------
Here is the code to get the data from the document and insert it:
---------------------------------------
DECLARE @docHandle int
declare @xmlDocument xml
set @xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS, ActivatedDate
FROM OPENXML(@docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
------------ ------------ --------------- ----------- -------------
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable, but I don't know what to do next. Any ideas? Thanks.
There is no "i" in team, but idiot has two.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The OPENXML statement needs to specify element-centric mapping (third parameter needs to be a 2)

When the WITH clause is not specified, the results are returned in an edge table format.

As you want a rowset format, a WITH (columnName datatype) needs to be included.

DECLARE @docHandle int

, @xmlDocument nvarchar(4000)

set @xmlDocument = N'<ROOT>

<SalesOfficeName>Roadway Main</SalesOfficeName>

</Customer>

</ROOT>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

Insert into Customer

( CustomerGUID

, ActivatedDate

, ActivatedByWS

, CustomerType

, SalesOfficeName )

SELECT CustomerGUID

, ActivatedDate

, ActivatedByWS

, CustomerType

, SalesOfficeName

FROM OPENXML(@docHandle, N'/ROOT/Customer',2)

with ( CustomerGUID uniqueidentifier

, ActivatedDate varchar(25)

, ActivatedByWS uniqueidentifier

, CustomerType char(2)

, SalesOfficeName varchar(25))

EXEC sp_xml_removedocument @docHandle

GO

select * from dbo.Customer

SQL = Scarcely Qualifies as a Language