在XML中将XML文档转换为表格数据集的有效方法，因为随着xml的增长，交叉应用xml查询的性能呈指数级下降 [英] efficient way to transform XML document into tabular dataset in SQL because cross apply xml query performs exponentially worse as xml grows
本文介绍了在XML中将XML文档转换为表格数据集的有效方法，因为随着xml的增长，交叉应用xml查询的性能呈指数级下降的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个很大的XML文档(50.000-100,000)，需要在如下所示的Azure SQL上进行解析:
I have a big size XML document (50.000-100,000) that needs to be parsed on Azure SQL that looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<covid-19 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://covid-19.iss.it/XMLSchema/0.1/">
<pazienti>
<paziente>
<codiceRegionalePaziente>0123456789</codiceRegionalePaziente>
<codiceFiscale/>
<nome>nomeBulk01</nome>
<cognome>cognomeBulk01</cognome>
<dataNascita>1989-12-31</dataNascita>
<sesso>F</sesso>
<nazionalita>380</nazionalita>
<domicilioIndirizzo/>
<domicilioCap>00019</domicilioCap>
<domicilioComune>058104</domicilioComune>
<domicilioProvincia>RM</domicilioProvincia>
<residenzaIndirizzo/>
<residenzaCap/>
<residenzaComune/>
<residenzaProvincia/>
<luogoEsposizione>cinema</luogoEsposizione>
<luogoEsposizioneComune>058047</luogoEsposizioneComune>
<operatoreSanitario>1</operatoreSanitario>
<casoIsolato>9</casoIsolato>
<casoCollegato/>
<codiceTampone>kkk12345</codiceTampone>
<dataPrelievo>2020-03-01</dataPrelievo>
<codLaboratorioAnalisi>999</codLaboratorioAnalisi>
<sequenzaGenoma>9</sequenzaGenoma>
<sequenzaInviata>0</sequenzaInviata>
<dataInizioSintomi>2020-03-01</dataInizioSintomi>
<codRegione>99</codRegione>
<patologieCroniche>9</patologieCroniche>
<tumoriAttivi/>
<diabeteMellito/>
<malattieCardiovascolari/>
<hiv/>
<malattieRespiratorieCroniche/>
<malattieRenali/>
<altreMalattieMetaboliche/>
<obesitaBmi30e40/>
<obesitaBmiOltre40/>
<malattieEpatiche/>
<malattieCronicheNeurologiche/>
<altrePatologie/>
<altrePatologieDescrizione/>
<note>test caricamento massivo da file xml</note>
<collocazioni>
<collocazione>
<dataCollocazione>2020-03-01</dataCollocazione>
<collocazioneTipo>Ospedale</collocazioneTipo>
<ospedaleNSIS>99999999</ospedaleNSIS>
<ospedaleReparto>Pneumologia</ospedaleReparto>
</collocazione>
</collocazioni>
<statiClinici>
<statoClinico>
<tipoStatoClinico>Asintomatico</tipoStatoClinico>
<dataStatoClinico>2020-03-01</dataStatoClinico>
<terapiaInCorso>0</terapiaInCorso>
<terapiaDescrizione/>
<intubato>0</intubato>
</statoClinico>
<statoClinico>
<tipoStatoClinico>Lieve</tipoStatoClinico>
<dataStatoClinico>2020-03-12</dataStatoClinico>
<terapiaInCorso>0</terapiaInCorso>
<terapiaDescrizione/>
<intubato>0</intubato>
</statoClinico>
<statoClinico>
<tipoStatoClinico>Critico</tipoStatoClinico>
<dataStatoClinico>2020-03-18</dataStatoClinico>
<terapiaInCorso>0</terapiaInCorso>
<terapiaDescrizione/>
<intubato>1</intubato>
</statoClinico>
</statiClinici>
</paziente>
<paziente>
<codiceRegionalePaziente>AABB1234567890</codiceRegionalePaziente>
<codiceFiscale>AAABBB00C11D222E</codiceFiscale>
<nome>nomeBulk02</nome>
<cognome>cognomeBulk02</cognome>
<dataNascita>2000-01-31</dataNascita>
<sesso>M</sesso>
<nazionalita>380</nazionalita>
<domicilioIndirizzo>Via del domicilio</domicilioIndirizzo>
<domicilioCap>00100</domicilioCap>
<domicilioComune>058091</domicilioComune>
<domicilioProvincia>RM</domicilioProvincia>
<residenzaIndirizzo/>
<residenzaCap/>
<residenzaComune/>
<residenzaProvincia/>
<luogoEsposizione>centro commerciale</luogoEsposizione>
<luogoEsposizioneComune>058091</luogoEsposizioneComune>
<operatoreSanitario>0</operatoreSanitario>
<casoIsolato>1</casoIsolato>
<casoCollegato/>
<codiceTampone>00AABB-CC</codiceTampone>
<dataPrelievo>2020-02-29</dataPrelievo>
<codLaboratorioAnalisi>999</codLaboratorioAnalisi>
<sequenzaGenoma>1</sequenzaGenoma>
<sequenzaInviata>0</sequenzaInviata>
<dataInizioSintomi>2020-02-29</dataInizioSintomi>
<codRegione>99</codRegione>
<patologieCroniche>1</patologieCroniche>
<tumoriAttivi>0</tumoriAttivi>
<diabeteMellito>0</diabeteMellito>
<malattieCardiovascolari>1</malattieCardiovascolari>
<hiv>0</hiv>
<malattieRespiratorieCroniche>1</malattieRespiratorieCroniche>
<malattieRenali>0</malattieRenali>
<altreMalattieMetaboliche>0</altreMalattieMetaboliche>
<obesitaBmi30e40>0</obesitaBmi30e40>
<obesitaBmiOltre40>0</obesitaBmiOltre40>
<malattieEpatiche>0</malattieEpatiche>
<malattieCronicheNeurologiche>0</malattieCronicheNeurologiche>
<altrePatologie>1</altrePatologie>
<altrePatologieDescrizione>descrizione altra patologia cronica</altrePatologieDescrizione>
<note>test caricamento massivo da file xml</note>
<collocazioni>
<collocazione>
<dataCollocazione>2020-03-01</dataCollocazione>
<collocazioneTipo>Domicilio</collocazioneTipo>
<ospedaleNSIS/>
<ospedaleReparto/>
</collocazione>
<collocazione>
<dataCollocazione>2020-03-05</dataCollocazione>
<collocazioneTipo>Ospedale</collocazioneTipo>
<ospedaleNSIS>99999999</ospedaleNSIS>
<ospedaleReparto>Malattie infettive e tropicali</ospedaleReparto>
</collocazione>
</collocazioni>
<statiClinici>
<statoClinico>
<tipoStatoClinico>Pauci-sintomatico</tipoStatoClinico>
<dataStatoClinico>2020-02-29</dataStatoClinico>
<terapiaInCorso>0</terapiaInCorso>
<terapiaDescrizione/>
<intubato>0</intubato>
</statoClinico>
<statoClinico>
<tipoStatoClinico>Severo</tipoStatoClinico>
<dataStatoClinico>2020-03-17</dataStatoClinico>
<terapiaInCorso>1</terapiaInCorso>
<terapiaDescrizione>descrizione della terapia in corso</terapiaDescrizione>
<intubato>0</intubato>
</statoClinico>
</statiClinici>
</paziente>
<paziente>
<codiceRegionalePaziente>9999999</codiceRegionalePaziente>
<codiceFiscale/>
<nome>nomeBulk03</nome>
<cognome>cognomeBulk03</cognome>
<dataNascita>2000-01-31</dataNascita>
<sesso>M</sesso>
<nazionalita>380</nazionalita>
<domicilioIndirizzo>Via del domicilio</domicilioIndirizzo>
<domicilioCap>00100</domicilioCap>
<domicilioComune>058091</domicilioComune>
<domicilioProvincia>RM</domicilioProvincia>
<residenzaIndirizzo/>
<residenzaCap/>
<residenzaComune/>
<residenzaProvincia/>
<luogoEsposizione>centro commerciale</luogoEsposizione>
<luogoEsposizioneComune>058091</luogoEsposizioneComune>
<operatoreSanitario>0</operatoreSanitario>
<casoIsolato>1</casoIsolato>
<casoCollegato/>
<codiceTampone>00AABB-CC</codiceTampone>
<dataPrelievo>2020-02-29</dataPrelievo>
<codLaboratorioAnalisi>999</codLaboratorioAnalisi>
<sequenzaGenoma>1</sequenzaGenoma>
<sequenzaInviata>0</sequenzaInviata>
<dataInizioSintomi>2020-02-29</dataInizioSintomi>
<codRegione>99</codRegione>
<patologieCroniche>1</patologieCroniche>
<tumoriAttivi>0</tumoriAttivi>
<diabeteMellito>0</diabeteMellito>
<malattieCardiovascolari>1</malattieCardiovascolari>
<hiv>0</hiv>
<malattieRespiratorieCroniche>1</malattieRespiratorieCroniche>
<malattieRenali>0</malattieRenali>
<altreMalattieMetaboliche>0</altreMalattieMetaboliche>
<obesitaBmi30e40>0</obesitaBmi30e40>
<obesitaBmiOltre40>0</obesitaBmiOltre40>
<malattieEpatiche>0</malattieEpatiche>
<malattieCronicheNeurologiche>0</malattieCronicheNeurologiche>
<altrePatologie>1</altrePatologie>
<altrePatologieDescrizione>descrizione altra patologia cronica</altrePatologieDescrizione>
<note>test caricamento massivo da file xml</note>
<collocazioni>
</collocazioni>
<statiClinici>
</statiClinici>
</paziente>
</pazienti>
</covid-19>
我需要将其拆分为3个常规表类型数据集(pazienti，collocazioni，statiClinici)，并且使用下面的有效T-SQL代码:
I need to split it into 3 regular table type dataset (pazienti, collocazioni, statiClinici) and I use the following T-SQL code that works:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @StartTime datetime = getdate()
-- Insert statements for procedure here
-- DROP temp tables
if object_id('tempdb..#xmlPazienti') is not null DROP TABLE #xmlPazienti;
if object_id('tempdb..#xmlCollocazioni') is not null DROP TABLE #xmlCollocazioni;
if object_id('tempdb..#xmlStatiClinici') is not null DROP TABLE #xmlStatiClinici;
-- pazienti --
CREATE TABLE #xmlPazienti
(
[patientId] [int] NULL
,[codiceFiscale] [varchar](16) NULL
,[nome] [nvarchar](255) NULL
,[cognome] [nvarchar](255) NULL
,[dataNascita] [datetime] NULL
,[sesso] [char](1) NULL
,[nazionalita] [smallint] NULL
,[domicilioInd] [varchar](255) NULL
,[domicilioCap] [varchar](10) NULL
,[domicilioCom] [varchar](255) NULL
,[domicilioProv] [varchar](255) NULL
,[residenzaInd] [varchar](255) NULL
,[residenzaCap] [varchar](10) NULL
,[residenzaCom] [varchar](255) NULL
,[residenzaProv] [varchar](255) NULL
,[luogoEsp] [nvarchar](500) NULL
,[luogoEspCom] [varchar](255) NULL
,[operatoreSanitario] [tinyint] NULL
,[casoIsolato] [tinyint] NULL
,[casoCollegato] [varchar](500) NULL
,[CodiceTampone] [varchar](255) NULL
,[dataPrelievo] [date] NULL
,[LaboratorioAnalisiId] [smallint] NULL
,[sequenzaGenoma] [tinyint] NULL
,[sequenzaInviata] [tinyint] NULL
,[DataInizioSintomi] [date] NULL
,[codRegione] [smallint] NULL
,[PatologieCroniche] [tinyint] NULL
,[TumoriAttivi] [tinyint] NULL
,[DiabeteMellito] [tinyint] NULL
,[MalattieCardiovascolari] [tinyint] NULL
,[HIV] [tinyint] NULL
,[MalattieRespiratorieCroniche] [tinyint] NULL
,[MalattieRenali] [tinyint] NULL
,[AltreMalattieMetaboliche] [tinyint] NULL
,[ObesitàBMI30e40] [tinyint] NULL
,[ObesitàBMIoltre40] [tinyint] NULL
,[MalattieEpatiche] [tinyint] NULL
,[MalattieCronicheNeurologiche] [tinyint] NULL
,[AltrePatologie] [tinyint] NULL
,[AltrePatologieDescrizione] [nvarchar](500) NULL
,[Note] [nvarchar](4000) NULL
,[patientID_reg] [nvarchar](50) NULL
,[flagBaseline] [int] NULL
)
-- pazienti --
-- collocazioni --
CREATE TABLE #xmlCollocazioni
(
dataRicovero date NULL
,collocazioneId int NULL
,idOspedale nvarchar(255) NULL
,CodReparto int NULL
,patientID int NULL
,patientID_reg nvarchar(50) NULL
,collocazioneTipo nvarchar(50) NULL
,ospedaleReparto nvarchar(255) NULL
);
-- collocazioni --
-- stati clinici --
CREATE TABLE #xmlStatiClinici
(
patientID int NULL
,statoClinicoId int NULL
,dataStatoClinico date NULL
,terapiaInCorso int NULL
,terapia nvarchar(1000) NULL
,Intubato int NULL
,patientID_reg nvarchar(50) NULL
,tipoStatoClinico nvarchar(50) NULL
);
-- stati clinici --
-- index on temp tables --
CREATE INDEX ixTmp_patient_patientId ON #xmlPazienti (patientId);
CREATE INDEX ixTmp_patient_patientID_reg ON #xmlPazienti (patientID_reg);
CREATE INDEX ixTmp_patient_codRegione ON #xmlPazienti (codRegione) INCLUDE (patientId);
CREATE INDEX ixTmp_patient_LaboratorioAnalisiId ON #xmlPazienti (LaboratorioAnalisiId);
CREATE INDEX ixTmp_ricovero_patientId ON #xmlCollocazioni (patientId);
CREATE INDEX ixTmp_ricovero_patientID_reg ON #xmlCollocazioni (patientID_reg);
CREATE INDEX ixTmp_ricovero_collocazioneID ON #xmlCollocazioni (collocazioneID);
CREATE INDEX ixTmp_ricovero_idOspedale ON #xmlCollocazioni (idOspedale);
CREATE INDEX ixTmp_ricovero_codReparto ON #xmlCollocazioni (codReparto);
CREATE INDEX ixTmp_monitoring_patientId ON #xmlStatiClinici (patientId);
CREATE INDEX ixTmp_monitoring_patientID_reg ON #xmlStatiClinici (patientID_reg);
CREATE INDEX ixTmp_monitoring_statoClinicoId ON #xmlStatiClinici (statoClinicoId);
-- index on temp tables --
DECLARE @StartTimeInsertPazienti datetime = getdate()
DECLARE @fId INT = 187
DECLARE @XML AS XML
SELECT @XML = XMLData FROM XMLbulkLoad WHERE Id = @fId
;WITH XMLNAMESPACES(DEFAULT 'http://covid-19.iss.it/XMLSchema/0.1/')
-- pazienti --
INSERT INTO #xmlPazienti WITH (TABLOCK) ([patientID_reg],[codiceFiscale],[cognome],[nome],[dataNascita],[sesso],[nazionalita],[domicilioInd],[domicilioCap],[domicilioCom],[domicilioProv],[residenzaInd],[residenzaCap],[residenzaCom],[residenzaProv],[luogoEsp],[luogoEspCom],[operatoreSanitario],[casoIsolato],[casoCollegato],[CodiceTampone],[dataPrelievo],[LaboratorioAnalisiId],[sequenzaGenoma],[sequenzaInviata],[DataInizioSintomi],[codRegione],[PatologieCroniche],[TumoriAttivi],[DiabeteMellito],[MalattieCardiovascolari],[HIV],[MalattieRespiratorieCroniche],[MalattieRenali],[AltreMalattieMetaboliche],[ObesitàBMI30e40],[ObesitàBMIoltre40],[MalattieEpatiche],[MalattieCronicheNeurologiche],[AltrePatologie],[AltrePatologieDescrizione],[Note])
SELECT ISNULL(paz.value('(codiceRegionalePaziente/text())[1]', 'nvarchar(50)'),NULL) AS [patientID_reg]
,ISNULL(paz.value('(codiceFiscale/text())[1]', 'varchar(16)'),NULL) AS [codiceFiscale]
,ISNULL(paz.value('(cognome/text())[1]', 'nvarchar(255)'),NULL) AS [cognome]
,ISNULL(paz.value('(nome/text())[1]', 'nvarchar(255)'),NULL) AS [nome]
,ISNULL(paz.value('(dataNascita/text())[1]', 'datetime'),NULL) AS [dataNascita]
,ISNULL(paz.value('(sesso/text())[1]', 'char(1)'),NULL) AS [sesso]
,ISNULL(paz.value('(nazionalita/text())[1]', 'smallint'),NULL) AS [nazionalita]
,ISNULL(paz.value('(domicilioIndirizzo/text())[1]', 'varchar(255)'),NULL) AS [domicilioInd]
,ISNULL(paz.value('(domicilioCap/text())[1]', 'varchar(10)'),NULL) AS [domicilioCap]
,ISNULL(paz.value('(domicilioComune/text())[1]', 'varchar(255)'),NULL) AS [domicilioCom]
,ISNULL(paz.value('(domicilioProvincia/text())[1]', 'varchar(255)'),NULL) AS [domicilioProv]
,ISNULL(paz.value('(residenzaIndirizzo/text())[1]', 'varchar(255)'),NULL) AS [residenzaInd]
,ISNULL(paz.value('(residenzaCap/text())[1]', 'varchar(10)'),NULL) AS [residenzaCap]
,ISNULL(paz.value('(residenzaComune/text())[1]', 'varchar(255)'),NULL) AS [residenzaCom]
,ISNULL(paz.value('(residenzaProvincia/text())[1]', 'varchar(255)'),NULL) AS [residenzaProv]
,ISNULL(paz.value('(luogoEsposizione/text())[1]', 'nvarchar(500)'),NULL) AS [luogoEsp]
,ISNULL(paz.value('(luogoEsposizioneComune/text())[1]', 'varchar(255)'),NULL) AS [luogoEspCom]
,ISNULL(paz.value('(operatoreSanitario/text())[1]', 'tinyint'),NULL) AS [operatoreSanitario]
,ISNULL(paz.value('(casoIsolato/text())[1]', 'tinyint'),NULL) AS [casoIsolato]
,ISNULL(paz.value('(casoCollegato/text())[1]', 'varchar(500)'),NULL) AS [casoCollegato]
,ISNULL(paz.value('(codiceTampone/text())[1]', 'varchar(255)'),NULL) AS [CodiceTampone]
,ISNULL(paz.value('(dataPrelievo/text())[1]', 'date'),NULL) AS [dataPrelievo]
,ISNULL(paz.value('(codLaboratorioAnalisi/text())[1]', 'smallint'),NULL) AS [LaboratorioAnalisiId]
,ISNULL(paz.value('(sequenzaGenoma/text())[1]', 'tinyint'),NULL) AS [sequenzaGenoma]
,ISNULL(paz.value('(sequenzaInviata/text())[1]', 'tinyint'),NULL) AS [sequenzaInviata]
,ISNULL(paz.value('(dataInizioSintomi/text())[1]', 'date'),NULL) AS [dataInizioSintomi]
,ISNULL(paz.value('(codRegione/text())[1]', 'smallint'),'') AS [codRegione]
,ISNULL(paz.value('(patologieCroniche/text())[1]', 'tinyint'),NULL) AS [patologieCroniche]
,ISNULL(paz.value('(tumoriAttivi/text())[1]', 'tinyint'),NULL) AS [tumoriAttivi]
,ISNULL(paz.value('(diabeteMellito/text())[1]', 'tinyint'),NULL) AS [diabeteMellito]
,ISNULL(paz.value('(malattieCardiovascolari/text())[1]', 'tinyint'),NULL) AS [malattieCardiovascolari]
,ISNULL(paz.value('(hiv/text())[1]', 'tinyint'),NULL) AS [hiv]
,ISNULL(paz.value('(malattieRespiratorieCroniche/text())[1]', 'tinyint'),NULL) AS [malattieRespiratorieCroniche]
,ISNULL(paz.value('(malattieRenali/text())[1]', 'tinyint'),NULL) AS [malattieRenali]
,ISNULL(paz.value('(altreMalattieMetaboliche/text())[1]', 'tinyint'),NULL) AS [altreMalattieMetaboliche]
,ISNULL(paz.value('(obesitaBmi30e40/text())[1]', 'tinyint'),NULL) AS [ObesitàBMI30e40]
,ISNULL(paz.value('(obesitaBmiOltre40/text())[1]', 'tinyint'),NULL) AS [ObesitàBMIoltre40]
,ISNULL(paz.value('(malattieEpatiche/text())[1]', 'tinyint'),NULL) AS [malattieEpatiche]
,ISNULL(paz.value('(malattieCronicheNeurologiche/text())[1]', 'tinyint'),NULL) AS [malattieCronicheNeurologiche]
,ISNULL(paz.value('(altrePatologie/text())[1]', 'tinyint'),NULL) AS [altrePatologie]
,ISNULL(paz.value('(altrePatologieDescrizione/text())[1]', 'nvarchar(500)'),NULL) AS [altrePatologieDescrizione]
,ISNULL(paz.value('(note/text())[1]', 'nvarchar(4000)'),NULL) AS [note]
FROM
@XML.nodes('covid-19/pazienti/paziente') AS A(paz)
--OPTION (OPTIMIZE FOR UNKNOWN)
--OPTION (RECOMPILE)
-- pazienti --
DECLARE @EndTimeInsertPazienti datetime = getdate()
SELECT DATEDIFF (SS ,@StartTimeInsertPazienti,@EndTimeInsertPazienti) AS PazientiInsertDuration
DECLARE @StartTimeInsertCollocazioni datetime = getdate()
-- collocazioni --
--SELECT @XML = XMLData FROM XMLbulkLoadDEV WHERE Id = @fID
--;WITH XMLNAMESPACES(DEFAULT 'http://covid-19.iss.it/XMLSchema/0.1/')
INSERT INTO #xmlCollocazioni WITH (TABLOCK) (patientID_reg, dataRicovero, collocazioneTipo, idOspedale, ospedaleReparto)
SELECT COALESCE(paz.value('(codiceRegionalePaziente/text())[1]', 'nvarchar(50)'),NULL) AS [patientID_reg]
,COALESCE(coll.value('(dataCollocazione/text())[1]', 'date'),NULL) AS [dataRicovero]
,COALESCE(coll.value('(collocazioneTipo/text())[1]', 'nvarchar(50)'),NULL) AS [collocazioneTipo]
,COALESCE(coll.value('(ospedaleNSIS/text())[1]', 'nvarchar(255)'),NULL) AS [idOspedale]
,COALESCE(coll.value('(ospedaleReparto/text())[1]', 'nvarchar(255)'),NULL) AS [ospedaleReparto]
FROM
@XML.nodes('covid-19/pazienti/paziente') AS A(paz)
OUTER APPLY
paz.nodes('collocazioni/collocazione') B(coll)
--OPTION (OPTIMIZE FOR UNKNOWN)
--OPTION (RECOMPILE)
-- collocazioni --
DECLARE @EndTimeInsertCollocazioni datetime = getdate()
SELECT DATEDIFF (SS ,@StartTimeInsertCollocazioni,@EndTimeInsertCollocazioni) AS CollocazioniInsertDuration
DECLARE @StarTimeInsertStatiClinici datetime = getdate()
-- stati clinici --
--SELECT @XML = XMLData FROM XMLbulkLoadDEV WHERE Id = @fID
--;WITH XMLNAMESPACES(DEFAULT 'http://covid-19.iss.it/XMLSchema/0.1/')
INSERT INTO #xmlStatiClinici WITH (TABLOCK) (patientID_reg, tipoStatoClinico, dataStatoClinico, terapiaInCorso, terapia, intubato)
SELECT ISNULL(paz.value('(codiceRegionalePaziente/text())[1]', 'nvarchar(50)'),NULL) AS [patientID_reg]
,ISNULL(sc.value('(tipoStatoClinico/text())[1]', 'nvarchar(50)'),NULL) AS [tipoStatoClinico]
,ISNULL(sc.value('(dataStatoClinico/text())[1]', 'date'),NULL) AS [dataStatoClinico]
,ISNULL(sc.value('(terapiaInCorso/text())[1]', 'int'),0) AS [terapiaInCorso]
,ISNULL(sc.value('(terapiaDescrizione/text())[1]', 'nvarchar(1000)'),NULL) AS [terapia]
,ISNULL(sc.value('(intubato/text())[1]', 'int'),NULL) AS [intubato]
FROM
@XML.nodes('covid-19/pazienti/paziente') AS A(paz)
OUTER APPLY
paz.nodes('statiClinici/statoClinico') C(sc)
--OPTION (OPTIMIZE FOR UNKNOWN)
--OPTION (RECOMPILE)
-- stati clinici --
DECLARE @EndTimeInsertStatiClinici datetime = getdate()
SELECT DATEDIFF (SS ,@StarTimeInsertStatiClinici,@EndTimeInsertStatiClinici) AS StatiCliniciInsertDuration
--
--select * from XMLbulkLoad
DECLARE @EndTimeInsert datetime = getdate()
SELECT DATEDIFF (SS ,@StartTimeInsertPazienti,@EndTimeInsertStatiClinici) AS TotalInsertDuration
-- DROP temp tables
if object_id('tempdb..#xmlPazienti') is not null DROP TABLE #xmlPazienti;
if object_id('tempdb..#xmlCollocazioni') is not null DROP TABLE #xmlCollocazioni;
if object_id('tempdb..#xmlStatiClinici') is not null DROP TABLE #xmlStatiClinici;
问题在于，当XML中有几百个(或更少)元素时，查询执行得很好.但是，当有25,000个元素时，要花50秒才能完成SSMS中的行返回，所以我可能有50-100,000个元素.
The problem is that when there are a few hundred (or fewer) elements in the XML, the query performs just fine. However, when there are 25,000 elements, it takes 50 seconds to finish returning the rows in SSMS and I could have 50-100,000 elements.
是否有更有效的方式将XML文档转换为表格数据集(在SQL中)?
Is there a more efficient way to transform the XML document into the tabular dataset (in SQL)?
推荐答案
一个一般提示可能是:在最后一步创建索引.尤其是对于很多行，这将减慢任何数据操作(在这种情况下，您的插入语句)；
One general hint might be: create your indexes as the final step. Especially with a lot of rows this will slow down any data manipulation (in this case your insert statements);
正如我在评论中所指出的，您可能会走登台路线.
As pointed in my comment you might walk the staging route.
首先，我创建一个模型表来模拟您的问题
First I create a mockup table to simulate your issue
DECLARE @tbl TABLE(YourXML XML);
INSERT INTO @tbl VALUES
(N'<covid-19 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://covid-19.iss.it/XMLSchema/0.1/">
<pazienti>
<paziente>
<codiceRegionalePaziente>0123456789</codiceRegionalePaziente>
<codiceFiscale/>
<nome>nomeBulk01</nome>
<cognome>cognomeBulk01</cognome>
<!-- shortened -->
<collocazioni>
<collocazione>
<dataCollocazione>2020-03-01</dataCollocazione>
<!-- shortened -->
</collocazione>
</collocazioni>
<statiClinici>
<statoClinico>
<tipoStatoClinico>Asintomatico</tipoStatoClinico>
<!-- shortened -->
</statoClinico>
<statoClinico>
<tipoStatoClinico>Lieve</tipoStatoClinico>
<!-- shortened -->
</statoClinico>
<statoClinico>
<tipoStatoClinico>Critico</tipoStatoClinico>
<!-- shortened -->
</statoClinico>
</statiClinici>
</paziente>
<paziente>
<codiceRegionalePaziente>AABB1234567890</codiceRegionalePaziente>
<codiceFiscale>AAABBB00C11D222E</codiceFiscale>
<nome>nomeBulk02</nome>
<cognome>cognomeBulk02</cognome>
<!-- shortened -->
<collocazioni>
<collocazione>
<dataCollocazione>2020-03-01</dataCollocazione>
<!-- shortened -->
</collocazione>
<collocazione>
<dataCollocazione>2020-03-05</dataCollocazione>
<!-- shortened -->
</collocazione>
</collocazioni>
<statiClinici>
<statoClinico>
<tipoStatoClinico>Pauci-sintomatico</tipoStatoClinico>
<!-- shortened -->
</statoClinico>
<statoClinico>
<tipoStatoClinico>Severo</tipoStatoClinico>
<!-- shortened -->
</statoClinico>
</statiClinici>
</paziente>
<paziente>
<codiceRegionalePaziente>9999999</codiceRegionalePaziente>
<codiceFiscale/>
<nome>nomeBulk03</nome>
<cognome>cognomeBulk03</cognome>
<!-- shortened -->
<collocazioni>
</collocazioni>
<statiClinici>
</statiClinici>
</paziente>
</pazienti>
</covid-19>');
-查询
WITH XMLNAMESPACES(DEFAULT 'http://covid-19.iss.it/XMLSchema/0.1/')
SELECT paz.value('(codiceRegionalePaziente/text())[1]', 'nvarchar(50)') AS [patientID_reg]
,paz.value('(codiceFiscale/text())[1]', 'varchar(16)') AS [codiceFiscale]
,paz.value('(cognome/text())[1]', 'nvarchar(255)') AS [cognome]
,paz.value('(nome/text())[1]', 'nvarchar(255)') AS [nome]
--shortended
,coll.value('(dataCollocazione/text())[1]', 'date') AS [dataRicovero]
--shortended
,sc.value('(tipoStatoClinico/text())[1]', 'nvarchar(50)') AS [tipoStatoClinico]
--shortended
--Define a target staging table simply automatically
INTO #StagingTable
FROM @tbl t
CROSS APPLY t.YourXML.nodes('covid-19/pazienti/paziente') AS A(paz)
OUTER APPLY
paz.nodes('collocazioni/collocazione') B(coll)
OUTER APPLY
paz.nodes('statiClinici/statoClinico') C(sc);
-现在您所有的数据都移到一张大表中了
--now all your data is shifted into one big table
SELECT * FROM #StagingTable
-使用类似的方法获取患者数据
--use something like this to get the patient data
SELECT patientID_reg,codiceFiscale,cognome,nome /*all patient related columns*/
FROM #StagingTable
GROUP BY patientID_reg,codiceFiscale,cognome,nome /*all patient related columns*/;
-这将是您的相关数据.
--This will your related data.
SELECT patientID_reg,dataRicovero /*all collocazioni related data*/
FROM #StagingTable
GROUP BY patientID_reg,dataRicovero /*all collocazioni related data*/
SELECT patientID_reg,tipoStatoClinico /*all stati clinici related data*/
FROM #StagingTable
GROUP BY patientID_reg,tipoStatoClinico /*all stati clinici related data*/
您可以根据暂存的数据执行任何清理或业务逻辑检查.检查目标表中现有的patientID_reg值可能是一个好主意...
然后将清除的数据移到最终目标表中.
You can perform any cleaning, or check of business logic, against the staged data. It might be a good idea to check for existing patientID_reg values in your target table...
Then shift the cleaned data into your final target tables.
注意:如果您的XML对于不同的元素可能带有相同的patientID_reg值，则必须添加ROW_NUMBER()
Attention: If your XML might carry the same patientID_reg value for different elements you will have to add a ROW_NUMBER()
在这种情况下，您可以使用以下方法:
In this case you might use something along this:
WITH XMLNAMESPACES(DEFAULT 'http://covid-19.iss.it/XMLSchema/0.1/')
,ReadPatientDataWithRowNumber AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PatientNumber
,paz.value('(codiceRegionalePaziente/text())[1]', 'nvarchar(50)') AS [patientID_reg]
,paz.value('(codiceFiscale/text())[1]', 'varchar(16)') AS [codiceFiscale]
,paz.value('(cognome/text())[1]', 'nvarchar(255)') AS [cognome]
,paz.value('(nome/text())[1]', 'nvarchar(255)') AS [nome]
--shortended
,paz.query('collocazioni/collocazione') AS CollocazioneXml
,paz.query('statiClinici/statoClinico') AS StatoClinicoXml
FROM @tbl t
CROSS APPLY t.YourXML.nodes('covid-19/pazienti/paziente') AS A(paz)
)
,AddTheRest AS
(
SELECT pd.*
,coll.value('(dataCollocazione/text())[1]', 'date') AS [dataRicovero]
--shortended
,sc.value('(tipoStatoClinico/text())[1]', 'nvarchar(50)') AS [tipoStatoClinico]
--shortended
FROM ReadPatientDataWithRowNumber pd
OUTER APPLY
pd.CollocazioneXml.nodes('collocazioni/collocazione') B(coll)
OUTER APPLY
pd.StatoClinicoXml.nodes('statiClinici/statoClinico') C(sc)
)
SELECT *
INTO #StagingTable
FROM AddTheRest
SELECT PatientNumber,patientID_reg,codiceFiscale,cognome,nome /*all patient related columns*/
FROM #StagingTable
GROUP BY PatientNumber,patientID_reg,codiceFiscale,cognome,nome /*all patient related columns*/;
SELECT PatientNumber,patientID_reg,dataRicovero /*all collocazioni related data*/
FROM #StagingTable
GROUP BY PatientNumber,patientID_reg,dataRicovero /*all collocazioni related data*/
SELECT PatientNumber,patientID_reg,tipoStatoClinico /*all stati clinici related data*/
FROM #StagingTable
GROUP BY PatientNumber,patientID_reg,tipoStatoClinico /*all stati clinici related data*/
更新2
请检查以下内容:
UPDATE 2
Please check this:
WITH XMLNAMESPACES(DEFAULT 'http://covid-19.iss.it/XMLSchema/0.1/')
,ReadPatientDataWithRowNumber AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PatientNumber
,paz.value('(codiceRegionalePaziente/text())[1]', 'nvarchar(50)') AS [patientID_reg]
,paz.value('(codiceFiscale/text())[1]', 'varchar(16)') AS [codiceFiscale]
,paz.value('(cognome/text())[1]', 'nvarchar(255)') AS [cognome]
,paz.value('(nome/text())[1]', 'nvarchar(255)') AS [nome]
--shortended
,paz.query('collocazioni/collocazione') AS CollocazioneXml
,paz.query('statiClinici/statoClinico') AS StatoClinicoXml
FROM @tbl t
CROSS APPLY t.YourXML.nodes('covid-19/pazienti/paziente') AS A(paz)
)
SELECT *
INTO #StagingTable
FROM ReadPatientDataWithRowNumber
SELECT * FROM #StagingTable;
这篇关于在XML中将XML文档转换为表格数据集的有效方法，因为随着xml的增长，交叉应用xml查询的性能呈指数级下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
