Query out attributes in XML

• dlongnecker-802303

  Ten Centuries

  Points: 1280

  More actions

  March 4, 2009 at 10:08 am

  #128237

  I have the following XML in my database:

  <PENDING_MODS>

  <DELETE TABLE="SOLUTION">

  <COLUMN CLAUSE="WHERE" NAME="SOLUTION_CDE" VALUE="1" />

  </DELETE>

  <DELETE TABLE="SOL_USERS">

  <COLUMN CLAUSE="WHERE" NAME="SOLUTION_CDE" VALUE="1" />

  <COLUMN CLAUSE="WHERE" NAME="USERS_CDE" VALUE="52" />

  </DELETE>

  </PENDING_MODS>

  And I want to Query out the NAME's and VALUES of all the COLUMNs. I've gotten this far:

  select XML.query('/PENDING_MODS/DELETE/COLUMN[@CLAUSE="WHERE"][1]') from table where id=33

  I can't find any information online on just getting attriubtes, though, only element values and actual XML. I could overhaul and go element centric, is that neccessary?

  ---
  Dlongnecker

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 4, 2009 at 3:45 pm

  #953662

  This example from BOL may solve your problem:

  DECLARE @myDoc xml

  DECLARE @ProdID int

  SET @myDoc = '<Root>

  <ProductDescription ProductID="1" ProductName="Road Bike">

  <Features>

  <Warranty>1 year parts and labor</Warranty>

  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

  </Features>

  </ProductDescription>

  </Root>'

  SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )

  SELECT @ProdID

  Greets

  Flo