Need Help on XML parsing

• gracebite

  Right there with Babe

  Points: 725

  More actions

  November 14, 2013 at 4:39 pm

  #285176

  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

• hemang.patel

  Old Hand

  Points: 364

  More actions

  November 14, 2013 at 9:19 pm

  #1666623

  Hi change your Extracting XML query

  --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]//AVAL') AS X(c))