Я хотел экспортировать все элементы, атрибуты XML из https://s3.amazonaws.com/irs-form-990/200931393493000150_public.xml. Как показано в моем коде ниже, я смог создать таблицы, используя несколько запросов. Похоже, мне нужно создать несколько таблиц из нескольких элементов и атрибутов, что будет утомительным процессом для объединения всех или объединения соответственно.
Мой вопрос: есть ли способ создать одну единственную таблицу за один раз всего содержимого XML?
Второй вопрос: как использовать openrowset для чтения из XML-файла, хранящегося в SQL Server, а не в локальном каталоге.
Вот код, который я использовал, который отлично работает для запроса части XML:
Create database [IRS-900]
USE [IRS-900]
GO
CREATE TABLE [SampleXML]
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO [SampleXMl](XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\Users\200931393493000150_public.xml', SINGLE_BLOB) AS x;
DECLARE @targetTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
EIN CHAR(10),
AddressLine1 VARCHAR(50),
AddressLine2 VARCHAR(50),
City VARCHAR(50),
State CHAR(2),
ZIPCode CHAR(5)
);
;WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
, rs (xmldata) AS
(
SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn
FROM OPENROWSET(BULK 'C:\Users\200931393493000150_public.xml', SINGLE_BLOB) AS x
-- xml file from (https://s3.amazonaws.com/irs-form-990/200931393493000150_public.xml) saved to local disk
)
INSERT INTO @targetTable
--Insert Into FormIndex
--Insert into IRS900Sample
SELECT
-- c.value('(Key/text())[1]', 'Varchar(100)') AS Keys
c.value('(EIN/text())[1]', 'CHAR(10)') AS EIN
, c.value('(USAddress/AddressLine1/text())[1]','VARCHAR(50)') AS AddressLine1
, c.value('(USAddress/AddressLine2/text())[1]','VARCHAR(50)') AS AddressLine2
, c.value('(USAddress/City/text())[1]','VARCHAR(100)') AS City
, c.value('(USAddress/State/text())[1]','CHAR(2)') AS State
, c.value('(USAddress/ZIPCode/text())[1]','CHAR(5)') AS ZIPCode
FROM rs AS tbl
--CROSS APPLY tbl.xmldata.nodes('/Return/ReturnHeader/Filer') AS t(c);
OUTER APPLY tbl.xmldata.nodes('/Return/ReturnHeader/Filer') AS t(c);
--querying the index
--CROSS APPLY tbl.xmldata.nodes ('/ListBucketResult/Contents') AS t(c);
--OUTER APPLY tbl.xmldata.nodes ('/ListBucketResult/Contents') AS t(c);
SELECT * FROM @targetTable;
--select * from FormIndex
--SELECT * FROM IRS900Sample
DECLARE @targetTable2 TABLE
--create table IRS900Sample
(
ID INT IDENTITY PRIMARY KEY,
NamePerson VARCHAR(50),
Title VARCHAR(50),
AverageHoursPerWeek FLOAT,
IndividualTrusteeOrDirector CHAR(2),
ReportableCompFromOrganization CHAR(2),
ReportableCompFromRelatedOrgs CHAR(2),
OtherCompensation CHAR(2)
);
;WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
, rs (xmldata) AS
(
SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn
FROM OPENROWSET(BULK 'C:\Users\200931393493000150_public.xml', SINGLE_BLOB) AS x
-- xml file from (https://s3.amazonaws.com/irs-form-990/200931393493000150_public.xml) saved to local disk
)
INSERT INTO @targetTable
--Insert Into FormIndex
--Insert into IRS900Sample
SELECT
c.value('(NamePerson/text())[1]','VARCHAR(50)') AS NamePerson
, c.value('(Title/text())[1]','VARCHAR(50)') AS Title
, c.value('(AverageHoursPerWeek/text())[1]','float') AS AverageHoursPerWeek
, c.value('(IndividualTrusteeOrDirector/text())[1]','CHAR(2)') AS IndividualTrusteeOrDirector
, c.value('(ReportableCompFromOrganization/text())[1]','CHAR(2)') AS ReportableCompFromOrganization
, c.value('(ReportableCompFromRelatedOrgs/text())[1]','CHAR(2)') AS ReportableCompFromRelatedOrgs
, c.value('(OtherCompensation/text())[1]','CHAR(2)') AS OtherCompensation
FROM rs AS tbl
OUTER APPLY tbl.xmldata.nodes('/Return/ReturnData/IRS990/Form990PartVIISectionA') AS t(c);
--CROSS APPLY tbl.xmldata.nodes('/Return/ReturnData/IRS990/Form990PartVIISectionA') AS t(c);
SELECT * FROM @targetTable2;
