locked
Problem in importing XML file RRS feed

  • Question

  • good morning, 

    i've a problem when i try to import an XML file to SQL.

    In my particular case i've an xml file structured like this:

    <?xml version="1.0" encoding="UTF-8"?>
    <Flussi xmlns="http://indennitario.acquirenteunico.it/Semplificato/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Flusso xsi:type="FlussoSI1.1150Type" COD_FLUSSO="1150" COD_SERVIZIO="SI1">
    <IdentificativiRichiesta>
    <CodPratGestore>688628</CodPratGestore>
    </IdentificativiRichiesta>
    </Flusso>
    </Flussi>

     I managed to import that file in that way:

    DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
    SELECT @XML = '<Flussi >

    <Flusso COD_FLUSSO="1150" COD_SERVIZIO="SI1">
    <IdentificativiRichiesta>
    <CodPratGestore>688628</CodPratGestore>
    </IdentificativiRichiesta>
    </Flusso>
    </Flussi>'

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

    SELECT
        Cod_flusso = Events.value('@COD_FLUSSO', 'int'),
        Cod_servizio = Events.value('@COD_SERVIZIO', 'varchar(20)'),
        --EventIsFixed = Events.value('(IsFixed)[1]', 'varchar(20)'),
        --EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),
        PIVAUtenteUscente = Events.value('(IdentificativiRichiesta/PIVAUtenteUscente)[1]', 'varchar(20)'),
        CodPratUtente=Events.value('(IdentificativiRichiesta/CodPratUtente)[1]', 'varchar(20)'),
        CodPOD=Events.value('(DatiTecnici/CodPOD)[1]', 'varchar(20)'),
        Cf=Events.value('(AnagraficaClienteFinale/CF)[1]', 'varchar(20)'),
        Cmor=Events.value('(DefinizioneIndennizzo/Cmor)[1]', 'float'),
        Credito=Events.value('(DefinizioneIndennizzo/Credito)[1]', 'float'),
        DataSwitching=Events.value('(DefinizioneIndennizzo/DataSwitching)[1]', 'varchar(20)'),
        DataEmissione=Events.value('(FattureIndennizzo/DataEmissione)[1]', 'varchar(20)'),
        TipoFattura=Events.value('(FattureIndennizzo/TipoFattura)[1]', 'varchar(20)'),
        DataScadenza=Events.value('(FattureIndennizzo/DataScadenza)[1]', 'varchar(20)'),
        IdentificativoFattura=Events.value('(FattureIndennizzo/IdentificativoFattura)[1]', 'varchar(20)'),
        NumeroGiorniErogazione=Events.value('(FattureIndennizzo/NumeroGiorniErogazione)[1]', 'int')
        --ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),
        --ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)')
    FROM
        @XML.nodes('/Flussi/Flusso') AS XTbl(Events)
    EXEC sp_xml_removedocument @hDoc

    The problem is that when i try to import the initial file with this kind of attributes: xsi:type="FlussoSI1.1150Type" i can't get the data.

    does anyone know how to solve the problem? thank you 

    • Moved by Kalman Toth Tuesday, November 25, 2014 10:40 AM Better fit
    Tuesday, November 25, 2014 8:32 AM

Answers

  • declare @x xml='<?xml version="1.0" encoding="UTF-8"?>
    <Flussi xmlns="http://indennitario.acquirenteunico.it/Semplificato/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Flusso xsi:type="FlussoSI1.1150Type" COD_FLUSSO="1150" COD_SERVIZIO="SI1">
    <IdentificativiRichiesta>
    <CodPratGestore>688628</CodPratGestore>
    </IdentificativiRichiesta>
    </Flusso>
    </Flussi>'
    ;WITH XMLNAMESPACES(default 'http://indennitario.acquirenteunico.it/Semplificato/1.0')
    select p.q.value('@COD_FLUSSO','int'),
    p.q.value('@xsi:type[1]','varchar(100)'),
    p.q.value('@COD_SERVIZIO','varchar(10)'),
    p.q.value('(IdentificativiRichiesta/CodPratGestore)[1]','int')
    from @x.nodes('/Flussi/Flusso')p(q)


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 25, 2014 10:06 AM
  • In addition to the other posts, I like to point out that you don't need these statements:

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
    EXEC sp_xml_removedocument @hDoc

    You use these procedures when you use OPENXML(), but you are using XQuery. (And stick with that.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 25, 2014 10:45 PM

All replies

  • Your XML file has a default namespace. Thus you need to use it in your query using the WITH XMLNAMESPACES() declaration:

    DECLARE @Xml AS XML = N'
    <Flussi xmlns="http://indennitario.acquirenteunico.it/Semplificato/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    	<Flusso xsi:type="FlussoSI1.1150Type" COD_FLUSSO="1150" COD_SERVIZIO="SI1">
    		<IdentificativiRichiesta>
    			<CodPratGestore>688628</CodPratGestore>
    		</IdentificativiRichiesta>
    	</Flusso>
    </Flussi>
    ';
    
    WITH XMLNAMESPACES ( DEFAULT 'http://indennitario.acquirenteunico.it/Semplificato/1.0' )
    	SELECT [Events].value('IdentificativiRichiesta[1]/CodPratGestore[1]', 'NVARCHAR(255)')
    	FROM    @Xml.nodes('/Flussi/Flusso') AS XTbl ( [Events] );

    btw, don't use reserved words as object names.

    Tuesday, November 25, 2014 8:57 AM
  • declare @x xml='<?xml version="1.0" encoding="UTF-8"?>
    <Flussi xmlns="http://indennitario.acquirenteunico.it/Semplificato/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Flusso xsi:type="FlussoSI1.1150Type" COD_FLUSSO="1150" COD_SERVIZIO="SI1">
    <IdentificativiRichiesta>
    <CodPratGestore>688628</CodPratGestore>
    </IdentificativiRichiesta>
    </Flusso>
    </Flussi>'
    ;WITH XMLNAMESPACES(default 'http://indennitario.acquirenteunico.it/Semplificato/1.0')
    select p.q.value('@COD_FLUSSO','int'),
    p.q.value('@xsi:type[1]','varchar(100)'),
    p.q.value('@COD_SERVIZIO','varchar(10)'),
    p.q.value('(IdentificativiRichiesta/CodPratGestore)[1]','int')
    from @x.nodes('/Flussi/Flusso')p(q)


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 25, 2014 10:06 AM
  • Thanks a Lot it works!!!
    Tuesday, November 25, 2014 10:30 AM
  • Thanks a Lot it works!!!

    Please mark relevant post as answer for others benefit

    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 25, 2014 11:41 AM
  • In addition to the other posts, I like to point out that you don't need these statements:

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
    EXEC sp_xml_removedocument @hDoc

    You use these procedures when you use OPENXML(), but you are using XQuery. (And stick with that.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 25, 2014 10:45 PM