OPENXML won't return rows

22 Nov 2005 6:40 PM

googleThis

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.

23 Nov 2005 12:41 AM

Graeme Malcolm

You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).

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', 2)
WITH Customer

--
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleT***@nadolna.net> wrote in message

Show quoteHide quote

news:1132684803.666917.108890@g47g2000cwa.googlegroups.com...
>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.
>

23 Nov 2005 3:11 AM

googleThis

That was it! Thanks very much, Graeme.