CODEWITHSUNDEEP
xmlsql-server-2008stored-procedures
Ankur
Ankur

Reputation: 148

Error while parsing XML through OPENXML

I have an XML of 55MB and is trying to shred it using OPENXML as it seems to be faster than normal XML shredding. The structure of XML is like this :

<DATA_EXPORT>
<HEADER>
    <RECDATE>
        <START>2011-03-16</START>
        <END>2012-02-10</END>
    </RECDATE>
    <SOME_COUNT>10288</SOME_COUNT>
    <QUESTION_MAP>
        <QUESTION>
            <SERVICE>OU</SERVICE>
            <VARNAME>UNIT</VARNAME>
            <QUESTION_TEXT></QUESTION_TEXT>
        </QUESTION>
                250 more nodes like <QUESTION> 
    </QUESTION_MAP>
</HEADER>
<SOMENODES>
    <SURVEY_ID>448817197</SURVEY_ID>
    <CLIENT_ID>58</CLIENT_ID>
    <SERVICE>OU</SERVICE>
    <RECDATE>2011-03-29</RECDATE>
    <DISDATE>2010-03-29</DISDATE>
</SOMENODES>
    :
    :
   1000s of nodes like <SOMENODES>
</DATA_EXPORT>

And I am using following query for getting the data out of the nodes

declare @xmlData varchar(max) = null
        ,@iDoc int  = null
        ,@xml xml = null

select @xmlData = cast(@xml as varchar(max))

    exec sp_xml_preparedocument @iDoc OUTPUT, @xmlData 

    select *
          ,getdate()
    from openxml(@iDoc, '//DATA_EXPORT/SOMEDATA', 2)
    with (
        surveyId        varchar(50)     'SURVEY_ID[1]',
        clientId        int         'CLIENT_ID[1]',
        [service]       varchar(50)     'SERVICE[1]',
        recieveDate     datetime        'RECDATE [1]',
        dischargeDate   datetime                'DISDATE [1]'
    )
    option (optimize for unknown)

I have wrritten this is stored procedure and am getting following error:

The XML parse error 0xc00ce562 occurred on line number 1, near the XML text "<".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'A declaration was not closed.'.
Msg 8179, Level 16, State 5, Procedure q_ImportSurveyMasterDetails, Line 81
Could not find prepared statement with handle 0.
The statement has been terminated.

Can anybody help me what is the error in line number as indicated by the error?? Also is there any better and faster way to shred a 55MB XML file??

Thanks in advance.

Upvotes: 2

Views: 9635

Answers (1)

Ankur
Ankur

Reputation: 148

Issue# 1: The XML parse error 0xc00ce562 occurred on line number 1, near the XML text "<". Fixed by changing @xmlData to @xml i.e. not converting it to varchar(max) just passing it as an xml. Thanks @dan radu for all your help.

Issue# 2: option keyword inside merge statement. It seems it cannot be used inside merge because merge statement is itself a single sql statement. It should be used at the end of the merge statement e.g.

merge dbo.table1 as target 
using (
        select 
            n.value('(SURVEY_ID)[1]', 'bigint') as surveyId
            ,n.value('(CLIENT_ID)[1]', 'int') as clientId
            ,n.value('(SERVICE)[1]', 'varchar(50)') as [service]
            ,n.value('(RECDATE)[1]', 'datetime') as recieveDate
            ,n.value('(DISDATE)[1]', 'datetime') as dischargeDate
        from @xml.nodes('//DATA_EXPORT/SOMENODES') x(n)

        ) as source 
 on target.surveyId = source.surveyId
    and target.[service] = source.[service]
 when matched then 
 update set 
        target.clientId = source.clientId,
        target.[service] = source.[service],
        target.surveyRecieveDate = source.recieveDate,
        target.dischargeDate = source.dischargeDate
when not matched then
insert (surveyId, clientId, [service], surveyRecieveDate, dischargeDate)
values (surveyId, clientId, [service], recieveDate, dischargeDate)
option (optimize for unknown);

Upvotes: 1

Related Questions