Help in removing the XMl node from the result set

• ramesh_kondaparthy

  Ten Centuries

  Points: 1069

  More actions

  January 11, 2006 at 7:26 am

  #170069

  HI all,

  I am working with XML datatype and OpenXml to covert the XML input to record set. The xml input contains the XML as inner value for one node as below.

  DEclare

  @strXML XML

  SET

  @strXML =

  '<root>

  <ApplicationFunctionality>

  <FunctionId>5</FunctionId>

  <FunctionName>MyFun1</FunctionName>

  <FunctionDescription>TestingRole_Test</FunctionDescription>

  <Parameters>

  <xml><Id>45</Id></xml>

  </Parameters>

  <Operation>1</Operation>

  </ApplicationFunctionality>

  </root>'

   

  declare

  @hDoc int

  EXEC

  sp_xml_preparedocument @hDoc OUTPUT, @strXML

  SELECT

  FunctionId, FunctionName, FunctionDescription, Parameters ,Operation

  FROM OPENXML(@hDoc, 'root/ApplicationFunctionality', 2)

  WITH (

  FunctionId

  INT,

  FunctionName

  NVARCHAR(150),

  FunctionDescription

  NVARCHAR(250),

  Parameters

  XML,

  Operation

  INT

  )

  When I ran the above query i am getting

  <

  Parameters> <xml> <Id>45</Id> </xml> </Parameters> as result. But i want to eliminate the <Parameters> </Parameters> from the query output.

  One more is the node after <Parameters>  is dynamic

   

  Please help me.

   

  Regards,

  Ramesh K

• peterhe

  SSChampion

  Points: 11469

  More actions

  January 12, 2006 at 7:00 am

  #614328

  You can  use xquery function to achieve this. CHange your query to:

  SELECT

  FunctionId, FunctionName, FunctionDescription, Parameters.query('/Parameters/xml')  AS Parameters,Operation

  FROM

  OPENXML(@hDoc, 'root/ApplicationFunctionality', 2)

  WITH

  (

  FunctionId

  INT,

  FunctionName

  NVARCHAR(150),

  FunctionDescription

  NVARCHAR(250),

  Parameters

  XML ,

  Operation

  INT

  )

   

• ramesh_kondaparthy

  Ten Centuries

  Points: 1069

  More actions

  January 12, 2006 at 7:22 am

  #614337

  Hi ,

  Thanks for the reply.

  But the issue here is the value of <Parameters> node is comming from the client. ('/Parameters/xml')

  So i can't hard code it as /Parameters/xml, some users may start their XML input with some other node.

   

  Regards,

  Ramesh K