This is what you are looking for

EXEC sp_xml_preparedocument @dochandle OUTPUT, @xmldocument;

SELECT

server,

name,

start_time,

type,

log_name,

media_mount_date,

drive_name,

media_label,

media_guid,

media_overwrite_date,

media_append_date,

media_set_target

FROM OPENXML(@dochandle, 'joblog', 1)

WITH

(

server [varchar](20) 'header/server/text()',

name [varchar](300) 'header/name/text()',

start_time [varchar](100) 'header/start_time/text()',

type varchar(20) 'header/type/text()',

log_name varchar(100) 'header/log_name/text()',

media_mount_date varchar(100) 'media_mount_date/text()',

-- some others you may be interested in...

drive_name varchar(100) 'media_drive_and_media_info/drive_name/text()',

media_label varchar(100) 'media_drive_and_media_info/media_label/text()',

media_guid varchar(100) 'media_drive_and_media_info/media_guid/text()',

media_overwrite_date varchar(100) 'media_drive_and_media_info/media_overwrite_date/text()',

media_append_date varchar(100) 'media_drive_and_media_info/media_append_date/text()',

media_set_target varchar(100) 'media_drive_and_media_info/media_set_target/text()'

)

EXEC sp_xml_removedocument @dochandle;

Note that I included the "text()" node... This is a good "best practice" for when your are dealing exclusively with atomic text values.