OPENXML query

• beath

  SSCrazy

  Points: 2939

  More actions

  April 14, 2008 at 9:59 am

  #186543

  Hi

  The following OPENXML statement works fine and returns each of the 6 song titles.

  DECLARE @hDoc int

  EXEC sp_xml_preparedocument @hDoc output,

  '

  '

  SELECT * FROM OPENXML(@hdoc, '/songs/song', 2) WITH

  (name varchar(80))

  EXEC sp_xml_removedocument @hDoc

  The xml I'm working with is formatted slightly different - each of the element as shown:

  DECLARE @hDoc int

  EXEC sp_xml_preparedocument @hDoc output,

  '

  '

  SELECT * FROM OPENXML(@hdoc, '/songs', 2) WITH

  (name varchar(80))

  EXEC sp_xml_removedocument @hDoc

  When I run this I only get the first song title returned. How can I get all 6 names returned as in the first example? Reformatting the xml is not an option as it is from a vendor app.

  thanks

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  April 23, 2008 at 4:50 am

  #805969

  Your XML did not make it through. Use the IFCode option from the toolbar and repost the example.

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com