how to query attributes from xml column

• csuresh79

  SSC Journeyman

  Points: 96

  More actions

  November 5, 2007 at 11:17 pm

  #183565

  Hi,

  we have loaded records from xml input file to xml field in a table

  find below the sample data from xml column

  " "

  " "

  " "

  " "

  " "

  " "

  Replace "

  Using TSQL, Can you please help me in querying these data as individual columns such as session_ID,visitor_id .

  Thanks

  SK.

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  November 5, 2007 at 11:52 pm

  #748521

  Your XML sample did not make it. (you need to escape the < with < )

  Concerning accessing attributes you can do this with the @ sign like:

  OPENXML(@iDoc, '//a:category', 1)

  WITH (

  [category] nvarchar(50) '.',

  [startdate] nvarchar(50) './@start',

  [enddate] nvarchar(50) './@end'

  ) XmlSet

  see http://www.sqlservercentral.com/Forums/Topic416002-145-1.aspx#bm416034 for one example.

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software