Need Help

• gracebite

  Right there with Babe

  Points: 725

  More actions

  November 14, 2013 at 4:37 pm

  #286246

  Hi Friends,

  I need help to get one (Identity_id) value from XML, I wrote below script but I am getting null Instead of 23456

  Can you please help me out on this.

  DECLARE @x VARCHAR(MAX),@xmldata as xml

  SELECT @x='<?xml version="1.0" encoding="UTF-8"?>

  <!DOCTYPE abcS SYSTEM "abc.dtd">

  <abcS>

  <abc NAME="XXX" SRC_FILE="abc.xml" SRC_TYPE="INTERNAL">

  <abc_ID ID="67184"/>

  <abc_NODE>

  <AVAL TYPE="EXACT">

  <AVAL_ID ID="67184"/>

  <SYN DISPLAY="TRUE" SEARCH="FALSE" CLASSIFY="FALSE">XXX</SYN>

  <B NAME="Identity_id">

  <BVAL>23456</BVAL>

  </B>

  <B NAME="display_name">

  <BVAL>XXX</BVAL>

  </B>

  </AVAL>

  </abc_NODE>

  </abc>

  </abcS>

  '

  SELECT @xmldata= CONVERT(XML,@X,2)

  DECLARE @Identity_id bigint

  --Extracting Identity_id from XML

  SET @Identity_id=(SELECT

  c.value('(B/BVAL)[1]', 'varchar(128)') Identity_id

  from @xmldata.nodes('(abcS/abc[@NAME="XXX"])[1]/abc_NODE[1]//abc_NODE/AVAL') AS X(c))

  SELECT @Identity_id

  Thanks

  Grace

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 17, 2013 at 5:59 am

  #1666934

  There's a duplicate reference to the abc_NODE node in your node reference

  ('(abcS/abc[@NAME="XXX"])[1]/abc_NODE[1]//abc_NODE/AVAL') AS X(c)) should be

  ('(abcS/abc[@NAME="XXX"])[1]/abc_NODE[1]/AVAL') AS X(c))

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]