OPENXML only give me NULL values, why ?

  • 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 ?

  • 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
  • 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.

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

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