OPENXML only give me NULL values, why ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » OPENXML only give me NULL values, why ?

OPENXML only give me NULL values, why ?

Rate Topic

Display Mode

Topic Options

Author

Message

onlo

Posted Tuesday, January 29, 2008 4:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 4:11 AM
Points: 187, Visits: 515

I typed the following statements :

CREATE TABLE ID (CHAR(4))
DECLARE @docHandle INT
DECLARE @xmlDocument XML
SET @xmlDocument=N'

'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
INSERT ID SELECT * FROM OPENXML(@docHandle, N'/IDs/ID') WITH ID

I list out the content of the ID table, there is just 2 NULL records.
Why ?

Post #448759

Mark-101232

Posted Tuesday, January 29, 2008 5:10 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 1,500, Visits: 18,185

Your XML doesn't show up, also I think WITH "tablename" will only pick up attributes.
Either change to this

INSERT ID SELECT * FROM OPENXML(@docHandle, N'/IDs/ID') WITH (ID char(4) 'text()')

or use the 'nodes' function

INSERT ID  
SELECT r.value('.','char(4)') AS ID
FROM @xmldocument.nodes('/IDs/ID') AS x(r)

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #448768

onlo

Posted Tuesday, January 29, 2008 6:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 4:11 AM
Points: 187, Visits: 515

Dear Mark,

My example was quoted from Microsoft's Web site :
http://msdn2.microsoft.com/en-us/library/ms191268.aspx

But you said : "Your XML doesn't show up, also I think WITH "tablename" will only pick up attributes."

I think what you said was correct.

Thanks a lot.

Post #449141

« Prev Topic | Next Topic »

Permissions