LutzM (10/7/2013)

---

Here's a slightly different approach without the OPENXML approach (I, personally, find OPENXML more complicted and it seems to be less efficient in most cases):

SELECT

TempXML1.Node1.value('(server/text())[1]', 'varchar(50)') AS [server],

TempXML1.Node1.value('(name/text())[1]', 'varchar(50)') AS [name],

TempXML1.Node1.value('(start_time/text())[1]', 'varchar(50)') AS [start_time],

TempXML1.Node1.value('(type/text())[1]', 'varchar(50)') AS [type],

TempXML1.Node1.value('(log_name/text())[1]', 'varchar(50)') AS [log_name],

TempXML2.Node2.value('(text())[1]', 'varchar(50)') AS myname ,

TempXML3.Node3.value('(media_mount_date/text())[1]', 'varchar(50)') AS media_mount_date,

TempXML3.Node3.value('(drive_name/text())[1]', 'varchar(50)') AS drive_name,

TempXML3.Node3.value('(media_label/text())[1]', 'varchar(50)') AS media_label,

TempXML3.Node3.value('(media_guid/text())[1]', 'varchar(50)') AS media_guid,

TempXML3.Node3.value('(media_overwrite_date/text())[1]', 'varchar(50)') AS media_overwrite_date,

TempXML3.Node3.value('(media_append_date/text())[1]', 'varchar(50)') AS media_append_date,

TempXML3.Node3.value('(media_set_target/text())[1]', 'varchar(50)') AS media_set_target

FROM @xmldocument.nodes('/joblog') TempXML (Node)

OUTER APPLY TempXML.Node.nodes('header') AS TempXML1(Node1)

OUTER APPLY TempXML.Node.nodes('media_mount_date') AS TempXML2(Node2)

OUTER APPLY TempXML.Node.nodes('media_drive_and_media_info') AS TempXML3(Node3)