Straight from BOL SQLSERVER2K8 ... "OPENXML" (/w substitution):
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<test>
<dbo.job>
<job_no>234234</job_no>
<crew_no>64850</crew_no>
<begindate></begindate>
<enddate></enddate>
</dbo.job>
<dbo.job>
<job_no>234</job_no>
<crew_no>234</crew_no>
<begindate></begindate>
<enddate></enddate>
</dbo.job>
<dbo.job>
<job_no>324</job_no>
<crew_no>234234</crew_no>
<begindate></begindate>
<enddate></enddate>
</dbo.job>
</test> '
That's the look to the data.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/test/DBO.JOB',2)
WITH (JOB_NO int,
CREW_NO int,
BEGINDATE datetime,
ENDDATE datetime)
The "OPENXML" help states "Execute a SELECT statement that uses the OPENXML rowset provider". There is another way to do all this. But it's not via OPENXML. See "XPath". Same ... JUST as complicated.
The output:
JOB_NO CREW_NO BEGINDATE ENDDATE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
234234 64850 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
234 234 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
324 234234 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000