Ok, we don't need to transform the xml first via xquery if you only want to shred the xml to a flat table. from your original post it appeared that you wanted to transform the xml that you had to a new format..

this piece of code will load your file and then shred the two values out to a table

DECLARE @xml XML

SELECT @xml = (SELECT * FROM OPENROWSET(BULK 'd:\chamila\XMLTest\text1.xsd', SINGLE_BLOB) x)

;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)

SELECT t.c.value('(d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text())[1]', 'int') as WorkerId

, t.c.value('(d1p1:Worker_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') as FullName

FROM @xml.nodes('//d1p1:Worker') t(c)

however I must ask that the filename you are loading has an xsd extension. normally this is used for xml schemas and not xml data. is this file an xml schema or does the file actually contain xml data like that you have posted previously.