parsing multiple xml tags using openxml in sql server

Question

<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> 234234 `

I want to know how to parse these tags using only OPEN XML and update them to a table DBO.JOB

Answer 1

Give this a try:

DECLARE @xml NVARCHAR(1000) =
'<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> 
</test>'

DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

INSERT  INTO DBO.JOB(JOB_NO, CREW_NO, BEGINDATE, ENDDATE)
SELECT  *
FROM    OPENXML(@hdoc, '/test/DBO.JOB', 2)
WITH    (
        JOB_NO INT,
        CREW_NO INT,
        BEGINDATE DATETIME,
        ENDDATE DATETIME
)

EXEC sp_xml_removedocument @hdoc