XML Parsing Issue

• SQL STEWIE

  SSCommitted

  Points: 1837

  More actions

  November 14, 2013 at 11:49 am

  #280204

  I am currently trying to parse the following XML using the following T-SQL code

  DECLARE @XML XML = '<?xml version="1.0"?>

  <root triggerEvent="UserChanged" xmlns="http://www.xyz.com">

  <AdminAudit_Ext>

  <ApplicationName>CC</ApplicationName>

  <ModifiedByUserID>su</ModifiedByUserID>

  <ModifiedByUserName>Super User</ModifiedByUserName>

  <ModifiedDate>2013-11-14T11:16:07.55-05:00</ModifiedDate>

  <ModifiedEntityID>1474</ModifiedEntityID>

  <ModifiedEntityName>User</ModifiedEntityName>

  <ModifiedEntityPublicID>test:30810</ModifiedEntityPublicID>

  <ModifiedFieldName>UserRole</ModifiedFieldName>

  <ModifiedObjectName>USER 1</ModifiedObjectName>

  <NewValue>Superuser</NewValue>

  </AdminAudit_Ext>

  </root>'

  SELECT Y.ID.value('(@triggerEvent)[1]', 'varchar(100)')

  FROM @xml.nodes('/root') Y(ID)

  and it is returning 0 rows. I am stumped as to what I am doing wrong. Can someone please provide me some insight?

  Thanks,

  Michael

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 14, 2013 at 3:36 pm

  #1666570

  The tricky part is the namespace declaration xmlns="http://www.xyz.com".

  This will force you to use the namespace within your query as well.

  The followingcode shoud work:

  ;

  WITH XMLNAMESPACES ('http://www.xyz.com' as ns)

  SELECT t.c.value('(@triggerEvent)[1]', 'varchar(100)')

  FROM @XML.nodes('/ns:root') t(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]

• SQL STEWIE

  SSCommitted

  Points: 1837

  More actions

  November 15, 2013 at 6:42 am

  #1666719

  Thanks!....that worked beautifully...but now when I try to run the following SQL, I get nothing. I know I suck at this XML parsing, so I appreciate the help

  ;WITH XMLNAMESPACES ('http://www.xyz.com' as ns)

  SELECT

  Y.ID.value('(@triggerEvent)[1]', 'varchar(100)') ,

  Y.ID.value('(ApplicationName)[1]', 'varchar(100)')

  FROM @xml.nodes('//ns:root/AdminAudit_Ext') Y(ID)

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  November 15, 2013 at 7:14 am

  #1666733

  Try this (notice I also changed the namespace declaration to use a default instead so you don't need to prefix things with "ns:")

  ;with xmlnamespaces ( default 'http://www.xyz.com')

  SELECT

  Y.ID.value('(@triggerEvent)[1]', 'varchar(100)')

  ,Y.ID.value('(AdminAudit_Ext/ApplicationName/text())[1]','varchar(100)')

  FROM @xml.nodes('/root') Y(ID)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]