Using sp_xml_preparedocument , OPENXML, XML node

• rhd110

  Ten Centuries

  Points: 1062

  More actions

  February 4, 2011 at 2:16 am

  #388780

  Friends,

  I have a certain query

  DECLARE @DocHandle int

  DECLARE @xmlString XML = N'<TAG1><A>1</A><A>2</A><A>3</A><A>4</A><A>5</A><A>6</A></TAG1>'

  EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlString

  SELECT*

  FROMOPENXML (@DocHandle, '/TAG1/A',3)

  WITH(AVARCHAR(5))

  EXEC sp_xml_removedocument @DocHandle

  The above query outputs NULL 6 times, however I want the result set as

  1

  2

  3

  4

  5

  6

  I exactly donot know what is wrong.

  If I do the same with

  DECLARE @xmlString XML = N'<TAG1><A>1</A><A>2</A><A>3</A><A>4</A><A>5</A><A>6</A></TAG1>'

  SELECT doc.col.value('A[1]','INT') AS A

  FROM @xmlString.nodes('/TAG1/A')doc(col)

  I have the same problem

  Can someone please help me.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 4, 2011 at 2:19 am

  #1282317

  You are referencing the node incorrectly. Try this (the text() function is used because it is slightly quicker ):

  DECLARE @xmlString XML = N'<TAG1><A>1</A><A>2</A><A>3</A><A>4</A><A>5</A><A>6</A></TAG1>'

  SELECT doc.col.value('./text()[1]','INT') AS A

  FROM @xmlString.nodes('/TAG1/A')doc(col)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]