Query XML data for blsnkd

• jdbrown239

  SSCarpal Tunnel

  Points: 4393

  More actions

  May 1, 2014 at 9:17 pm

  #292938

  Hi

  I am try to see if there are any blanks in a node of a table that has xml data in one of the columns. The query I use is returning zero results. Any suggestions?

  Select COUNT(*)from ENTITY

  Where CONVERT(XML, Ent_root_xml, 0 ).value('(//UD_PQ_FLAG/node())[1]', 'VARCHAR(50)')= ''

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  May 4, 2014 at 5:37 am

  #1710566

  Quite few ways of doing this, here is one

  😎

  DECLARE @XML_STUFF TABLE

  (

  XML_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

  ,XML_DATA XML NOT NULL

  )

  INSERT INTO @XML_STUFF (XML_DATA)

  VALUES

  (N'<ROOT>

  <ELEMENT01>

  <DETAIL>HAS STUFF</DETAIL>

  </ELEMENT01>

  </ROOT>')

  ,(N'<ROOT>

  <ELEMENT01>

  <!-- HAS NO STUFF -->

  </ELEMENT01>

  </ROOT>')

  ,(N'<ROOT>

  <ELEMENT01>

  <DETAIL>HAS STUFF</DETAIL>

  </ELEMENT01>

  </ROOT>');

  SELECT

  XS.XML_ID

  ,DET.AIL.value('.[1]','NVARCHAR(128)') AS DETAIL_TEXT

  FROM @XML_STUFF XS

  OUTER APPLY XS.XML_DATA.nodes('ROOT/ELEMENT01/DETAIL') AS DET(AIL);

  Results

  XML_ID DETAIL_TEXT

  ----------- ------------

  1 HAS STUFF

  2 NULL

  3 HAS STUFF