Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENXML only give me NULL values, why ? Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 4:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
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
Posted Tuesday, January 29, 2008 5:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 1,683, Visits: 19,607
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
Posted Tuesday, January 29, 2008 6:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
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 »

Add to briefcase

Permissions Expand / Collapse