processing xml

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  January 17, 2014 at 11:42 pm

  #281263

  hello all.

  I have this script:

  DECLARE @XML AS XML, @hDoc AS INT, @sql NVARCHAR (MAX)

  set @XML ='<Root>

  <WPID>4</WPID>

  <WPName>دي92</WPName>

  <Sum>0</Sum>

  <Result>03:30 , 18</Result>

  <Count>1</Count>

  </Root>'

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

  SELECT WPID, WPName, Sum, Result, Count

  FROM OPENXML(@hDoc, 'ROOT/Count/Result/Sum/WPName/WPID')

  WITH

  (

  WPID int '@WPID',

  WPName [varchar](100) '@WPName',

  Sum float '@Sum',

  Result [varchar](1000) '@Result',

  Count int '@Count'

  )

  EXEC sp_xml_removedocument @hDoc

  GO

  but it dosen,t get result,what is my wrong?thanks

• pmadhavapeddi22

  SSCertifiable

  Points: 5306

  More actions

  January 18, 2014 at 4:46 am

  #1681324

  Hi

  I tried the query like the below

  DECLARE @idoc int, @doc varchar(1000);

  SET @doc ='

  <ROOT>

  <AA wpid="4" WPName="??92" sum="0" result="03:30 , 18" count="1">

  </AA>

  </ROOT>';

  --Create an internal representation of the XML document.

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

  -- Execute a SELECT statement that uses the OPENXML rowset provider.

  SELECT *

  FROM OPENXML (@idoc, '/ROOT/AA',1)

  WITH (

  wpid int,

  wpname varchar(100),

  sum float,

  result varchar(1000),

  count int);

  It gave result. But could not find out what's wrong in your script

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  January 20, 2014 at 12:59 am

  #1681442

  thanks for your reply.but I have fix xml file from another program,s out put and I can't change structure of xml.what should I do?

• KS

  Hall of Fame

  Points: 3323

  More actions

  January 20, 2014 at 6:24 am

  #1681507

  Try this

  DECLARE @XML AS XML, @hDoc AS INT

  SET @XML =

  '<Root>

  <WPID>4</WPID>

  <WPName>??92</WPName>

  <Sum>0</Sum>

  <Result>03:30 , 18</Result>

  <Count>1</Count>

  </Root>'

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

  SELECT WPID, WPName, Sum, Result, Count

  FROM OPENXML(@hDoc, 'Root')

  WITH

  (

  WPID int 'WPID',

  WPName [varchar](100) 'WPName',

  Sum float 'Sum',

  Result [varchar](1000) 'Result',

  Count int 'Count'

  )

  EXEC sp_xml_removedocument @hDoc

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  January 20, 2014 at 10:43 pm

  #1681729

  thanks alot.it works.:-)