OPENXML only give me NULL values, why ?

• onlo

  SSCertifiable

  Points: 5080

  More actions

  January 29, 2008 at 4:38 am

  #186070

  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 ?

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 29, 2008 at 5:10 am

  #772790

  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)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

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

• onlo

  SSCertifiable

  Points: 5080

  More actions

  January 29, 2008 at 6:39 pm

  #773090

  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.