Problem in importing XML file

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 

Answer 1

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.

Answer 2

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

Answer 3

Thanks a Lot it works!!!

Answer 4

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

Answer 5

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, [e-mail]