Разложение XML-данных в DB2 с помощью технологии pureXML и функции XMLTable

Преобразование XML-данных в реляционные данные

Возможности продукта IBM® DB2® по работе с форматом XML упрощают преобразование XML-данных в реляционные данные. Так, встроенная функция XMLTABLE предоставляет простой в использовании и мощный механизм для преобразования иерархических XML-данных в реляционные данные типа "родитель-потомок". Если ваши инструменты не поддерживают работу с форматом XML или вам требуется обработка реляционных данных, то функция XMLTABLE позволит восполнить этот пробел.

Роберт Уилсон, сертифицированный ИТ-специалист, экспертный центр SWG, IBM

Rob Wilson photoРоберт Уилсон (Robert G. Wison) является квалифицированным специалистом по DB2 Linux, UNIX и Windows с более чем 10-летним опытом. В последнее время он участвует в нескольких проектах, посвященных перспективным способам применения технологии pureXML. Кроме того, Р. Уилсон уделяет много времени технологии pureScale.



01.07.2013

Введение

Технология DB2 pureXML позволяет хранить правильно построенные XML-документы внутри реляционной базы данных в собственном ("родном") формате. В рамках этой базы данных вышеуказанными документами можно манипулировать с помощью языков SQL и XQUERY. При отсутствии такой функциональности приложению пришлось бы получить сначала полный документ и внести в него изменении, а затем вернуть этот полный документ в базу данных. В случае больших документов или больших объемов такой подход способен быстро исчерпать ресурсы сетевых соединений.

Технология pureXML предусматривает поддержку SQL-функций XMLQUERY, XMLTABLE и XMLEXISTS. Эти функции используются в операторах SELECT, FROM и WHERE соответственно. Функция XMLTABLE позволяет нескольким выражениям XPath или XQuery специфицировать элементы документа, после чего эти элементы будут доступны точно так же, как если бы они находились в реляционной таблице. Одно из выражений XQuery в функции XMLTABLE действует как генерирующее строку выражение, другими словами, функция XMLTABLE возвращает по одной строке для каждого элемента, возвращаемого этим выражением XQuery. Используя несколько вызовов функции XMLTABLE, можно имитировать отношения с внешним ключом, которые доступны в инструментах на основе SQL. Более подробную информацию о функции XMLTABLE можно получить из статьи на сайте developerWorks под названием XMLTABLE by example (Функция XMLTABLE в примерах).

Иногда преобразование XML в реляционные данные обозначается термином "разложение" (shredding). Применение технологии DB2 pureXML позволяет "разлагать" документ без его извлечения из базы данных и сохранять результаты этого разложения в реляционных таблицах.

Сообщения

ASDI-сообщения

В этой статье при исследовании вышеупомянутого "разложения" XML-документов будет использоваться стандарт ASDI (Aircraft Situation Display to Industry) и его схема. ASDI – это строго определенный стандарт, основанный на технологии XML и предназначенный для обмена информацией о воздушном движении между участниками авиационной отрасли посредством потоков данных. Информация поступает в виде потока, состоящего из множества сообщений (Рисунок 1). Предусмотрено 11 типовых сообщений, которые составляются из 81 базового элемента.

Сообщения типа arrivalInformation

Один из типов сообщений в ASDI-потоке – это т.н. сообщения о прибытии. Сообщения этого типа обозначаются как arrivalInformationType (Рисунок 2). Этот составной тип определяется посредством двух элементов типа qualifiedAircraftIdType (Рисунок 3) и элемента типа timeOfArrivalType. Элемент qualifiedAircraftIdType состоит из элемента computerIdType (Рисунок 4), информации о местоположении пункта отправления и информация о местоположении пункта прибытия.

Рисунок 1. Блок-схема типичного потока сообщения
Block diagram showing multiple arrivalInformation messages in an asdiMessage packet
Рисунок 2. Схема элемента arrivalInformationType
Diagram of the ArrivalInformationType showing it is made up of qualifiedAircraftIdType and timeOfArrivalType
Рисунок 3. Схема элемента qualifiedAircraftIdType
Diagram of the qualifiedAircraftId Type showing it is made up of flightClass, aircraftIdType, flightComputerIdType, departurePointType, and arrivalPointType
Рисунок 4. Схема элемента computerIdType
Diagram of the computerId Type showing it is made up of facilityIdentifiedType, comptuerSystemIdType, and idNumberType

Разложение документов

Сценарий: Аналитик желает выполнить несколько запросов с целью обобщения информации о прибытии. Ему требуется лишь подмножество имеющихся данных. Нужные данные будут агрегированы для отчетов. Это однократный процесс, который будет выполняться время от времени с низким приоритетом.

Определение важных элементов данных

Приложению нет необходимости знать обо всех элементах в схеме документа. Первый шаг состоит в составлении списка важных элементов и выражения XPATH для отыскания этих элементов в XML-документе. На следующем шаге следует рассмотреть дополнительные элементы, которые могут потребоваться для агрегации или для фильтрации.

После того как важные элементы заданы, типы их реляционных данных могут быть определены из XML-схемы, из эталонных документов и из предоставленных преобразований. Затем тесно связанные элементы могут быть сгруппированы в таблицы, а связанные таблицы, подобно методам, могут быть использованы для традиционного моделирования реляционной базы данных. Весьма полезным является следующее представление (табл. 1), которое отслеживает таблицу, столбец и тип реляционных данных с помощью выражения XPATH для исходного элемента. При необходимости изменения порядка возвращения данных соответствующее преобразование также может быть отображено в указанной таблице.

В рамках ASDI-схемы элемент computerId имеет три элемента, которые являются строками и могут быть представлены в реляционной базе данных как одна таблица.

Таблица 1. Таблица, демонстрирующая отображение элемента computerId из реляционного столбца на выражение XPATH
ТаблицаСтолбецТип данныхXPATH
computeridfacilityIdVARCHAR(4)/adiMessage/arrivalInformation/nxcm:qualifiedAircraftId/nxce:computerId/nxce:facilityIdentifier
computeridcomputerSystemIdVARCHAR(5)/asdiMessage/arrivalInformation/nxcm:qualifiedAircraftId/nxce:computerId/nxce:computerSystemId
computerididNumberVARCHAR(3)/asdiMessage/arrivalInformation/nxcm:qualifiedAircraftId/nxce:computerId/nxce:idNumber
Листинг 1. Таблица ComputerId
CREATE TABLE computerid (
	facilityID       VARCHAR(4),
	computersystemID VARCHAR(5),
	idnumber	 VARCHAR(3)
)
Листинг 2. Запрос XMLTable для заполнения таблицы ComputerId
INSERT INTO computerid (facilityID, computersystemID, idnumber)
SELECT X.facilityID, X.computersystemID, x.idnumber
FROM XMLTABLE (
    XMLNAMESPACES( 'http://tfm.faa.gov/tfms/NasXCoreElements'	AS "nxce",
                   'http://tfm.faa.gov/tfms/MessageMetaData'	AS "mmd",
                   'http://tfm.faa.gov/tfms/NasXCommonMessages'	AS "nxcm",
                   'http://www.w3.org/2001/XMLSchema-instance'	AS "xsi" ),
  '$DOC/*:asdiOutput/*:asdiMessage/*:arrivalInformation/
  	nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING CAST(? as XML) as "DOC"
  COLUMNS
	facilityID	 VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	idNumber	 VARCHAR(3)	PATH 'nxce:idNumber'
) as X

Примечание: Для краткости во всех остальных листингах кода декларация XMLNAMESPACES не будет показана.

Связывание элементов в иерархиях

Сценарий: Теперь аналитик желает выполнить более сложный анализ данных computerId и данных qualifiedAircraftId. Поскольку в элементе qualifiedAircraftId может быть несколько элементов computerId, для них требуются свои собственные таблицы.

При исследовании списка интересующих нас элементов иерархия должна быть очевидной. Теперь следует сгруппировать элементы в рамках логических групп или одного и того же типа XML-данных.

Например, все элементы в рамках элемента computerId могут быть сгруппированы под элементом qualifiedAircraftId, который находится под arrivalInformation. Элемент departurePoint находится под элементом qualifiedAircraftId, но не связан с элементом computerId, поэтому его следует группировать отдельно от элементов computerId. Элемент arrivalPoint должен быть сгруппирован аналогичным образом, но отдельно от элемента departurePoint и от элемента computerId. Поскольку элемент arrivalPoint и элемент departurePoint совместно используют одни и те же элементы, они могут храниться вместе в одной таблице и использовать для своего декларирования дополнительный столбец.

В случае хранения этих элементов в разных таблицах нужен т.н. суррогатный ключ (surrogate key) для связывания родительского и дочернего элемента. Функция GENERATE_UNIQUE () создает идентификатор UUID (universally unique identifier), который может быть использован в качестве такого суррогатного ключа. Каждому уровню в хранящейся иерархии придется ссылаться на вышестоящий уровень с целью поддержания ссылочной целостности в рамках документа.

Модель данных на рисунке 5 может быть создана с помощью SQL-кода, показанного в листинге 3.

Рисунок 5. QualifiedAircraft и ComputerId, связанные посредством UUID-идентификаторов
Diagram of the schema of the qualifiedAircraft and ComputerId tables showing a foreign-key relationship on the generated UUID
CREATE TABLE computerid (
	parentUUID	 CHAR(13) FOR BIT DATA,
	facilityID	 VARCHAR(4),
	computersystemID VARCHAR(5),
	idnumber	 VARCHAR(3)
);

CREATE TABLE qualifiedAircraft (
	QA_UUID 	CHAR(13) FOR BIT DATA NOT NULL PRIMARY KEY,
	aircraftID	VARCHAR(6),
	flightClass	VARCHAR(16)
);
alter table computerid add  foreign key (parentUUID) 
	references qualifiedAircraft(QA_UUID);

Код в листинге 4 используется для заполнения этих двух таблиц и для поддержания отношений внешнего ключа на основе сгенерированного UUID-идентификатора.

Листинг 4. Сложные запросы XMLTable для заполнения связанных таблиц computerId и qualifiedAircraftId
-- The statement terminator character is @
begin atomic
declare QA_UUID CHAR(16) FOR BIT DATA;

SET (QA_UUID) = (
SELECT QA_UUID FROM NEW TABLE (
  INSERT INTO qualifiedaircraft (QA_UUID, aircraftID, flightClass)
  SELECT GENERATE_UNIQUE(), X.aircraftID, X.flightClass
  FROM XMLTABLE (
    '$DOC/*:asdiOutput/*:asdiMessage/*:arrivalInformation/nxcm:qualifiedAircraftId'
	PASSING CAST(? as XML) as "DOC"
    COLUMNS
	  aircraftID	VARCHAR(6)	PATH 'nxce:aircraftId',
	  flightClass	VARCHAR(16)	PATH '@flightClass'
  ) as X
)
);

INSERT INTO computerid (parentUUID, facilityID, computersystemID, idnumber)
SELECT QA_UUID, X.facilityID, X.computersystemID, x.idnumber
FROM XMLTABLE (
  '$DOC/*:asdiOutput/*:asdiMessage/*:arrivalInformation/
  	nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING XMLDOCUMENT(?) as "DOC"
  COLUMNS
	facilityID	 VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	idNumber	 VARCHAR(3)	PATH 'nxce:idNumber'
) as X
;

end@

Код в листинге 4 будет должным образом поддерживать отношение внешнего ключа до тех пор, пока в документе будет существовать единственный элемент qualifiedAircraftId. В случае нескольких элементов computerId каждый из них будет указывать обратно на этот элемент qualifiedAircraftId. В случае нескольких элементов qualifiedAircraftId потребуется задание цикла для выполнения вставки computerId. Эта ситуация демонстрируется в листинге 13.

Планирование будущего разложения

Сценарий: Владельцы приложений высоко оценили отчеты, созданные аналитиком. Теперь они требуют дальнейшего превращения XML-данных в реляционную форму. Они еще не решили, какие фрагменты важны для них, однако им известно некоторое их подмножество. Они хотят приступить к этой работе и в процессе реализации выяснить, чего им не хватало для использования.

Каждый представляющий интерес элемент в рамках документа может быть сохранен в форме XML для будущего разложения (Рисунок 6). Это можно сделать посредством задания дополнительного столбца XML в реляционной таблице (Листинг 5). Такое хранение элементов может применяться в качестве эффективного способа заполнения любых столбцов, которые могут быть добавлены к схеме в будущем.

Рисунок 6. Элементы QualifiedAircraft и ComputerId с фрагментами документа
Diagram of the schema of the qualifiedAircraft and ComputerId tables showing additiona of XML columns
Листинг 5. Таблицы ComputerId и qualifiedAircraft со столбцами UUID и XML
CREATE TABLE computerid (
	parentUUID	 CHAR(16) FOR BIT DATA,
	facilityID	 VARCHAR(4),
	computersystemID VARCHAR(5),
	idnumber	 VARCHAR(3),
	doc		 XML
);

CREATE TABLE qualifiedAircraft (
	QA_UUID 	CHAR(16) FOR BIT DATA,
	aircraftID	VARCHAR(6),
	flightClass	VARCHAR(16),
	doc		XML
);

Для заполнения столбца XML в этих двух таблицах предыдущий запрос можно модифицировать добавлением нового выражение COLUMNS к функции XMLTABLE. Соответствующий результат показан в листинге 6.

Листинг 6. Составные запросы XMLTable для заполнения XML-данными таблиц computerId и qualifiedAircraftId
-- The statement terminator character is @
begin atomic
declare V_UUID CHAR(16) FOR BIT DATA;

SET (V_UUID) = (
SELECT QA_UUID FROM NEW TABLE (
  INSERT INTO qualifiedaircraft (QA_UUID, aircraftID, flightClass, doc)
  SELECT GENERATE_UNIQUE(), X.aircraftID, X.flightClass, XMLDOCUMENT(X.doc)
  FROM XMLTABLE (
    '$DOC/*:asdiOutput/*:asdiMessage/*:arrivalInformation/nxcm:qualifiedAircraftId'
	PASSING CAST(? as XML) as "DOC"
    COLUMNS
	  aircraftID	VARCHAR(6)	PATH 'nxce:aircraftId',
	  flightClass	VARCHAR(16)	PATH '@flightClass',
	  DOC		XML		PATH '.'
  ) as X
 )
);

INSERT INTO computerid (parentUUID, facilityID, computersystemID, idnumber, doc)
SELECT QA.QA_UUID, X.facilityID, X.computersystemID, x.idnumber, XMLDOCUMENT(x.doc)
FROM qualifiedaircraft qa, XMLTABLE (
  '$DOC/nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING QA.DOC as "DOC"
  COLUMNS
	facilityID	 VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	idNumber	 VARCHAR(3)	PATH 'nxce:idNumber',
	DOC		 XML		PATH '.'
 ) as X
WHERE QA.QA_UUID = V_UUID
;

end@

В этом примере по-прежнему используется UUID-идентификатор, сгенерированный для элемента qualifiedAircraft, поэтому он не будет работать в случае нескольких элементов qualifiedAircraft в одном и том же документе. Можно с помощью приложения заполнить временную таблицу или массив всеми сгенерированными UUID-идентификаторов, а затем заполнить аналогичным способом таблицу computerid, подставив соответствующую логику для переменной V_UUID.

К схеме могут быть добавлены и другие поддерживаемые таблицы и столбцы, чтобы приложение могло ставить в очередь и распределять свои действия по разложению. Например, можно добавить столбец метки времени для отслеживания момента времени, когда столбец DOC анализировался в последний раз.

Различные аспекты обеспечения производительности

Производительность функции XMLTABLE зависит от характеристик ее входной информации, в том числе от размера и от сложности анализируемого документа, от количества возвращаемых столбцов, от выражений XPATH, используемых для задания этих столбцов в рамках документа. Рассмотрим несколько рекомендаций по достижению максимальной производительности.

Использование XMLQUERY при ссылке на более высокие элементы в иерархии

Если элемент в выражении COLUMNS функции XMLTABLE находится в иерархии выше, чем корневой ссылочный элемент документа (т. е., выражение PATH начинается с символов “..”), то более эффективным может оказаться использование функции XMLQUERY. Это особенно заметно, когда результатом выполнения функции XMLTABLE являются несколько строк.

Листинг 7. Запрос для случая, когда в таблице computerID также должен присутствовать XML-документ qualifiedAircraft.
INSERT INTO computerid (parentUUID, facilityID, computersystemID, idnumber, 
	computerdoc, qualifiedaircraftdoc)
SELECT QA.QA_UUID, X.facilityID, X.computersystemID, x.idnumber, 
	XMLDOCUMENT(x.computerdoc), XMLDOCUMENT(x.qualifiedAircraftDoc)
FROM qualifiedaircraft qa, XMLTABLE (
  '$DOC/nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING QA.DOC as "DOC"
  COLUMNS
	facilityID	 VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	idNumber	 VARCHAR(3)	PATH 'nxce:idNumber',
	computerDOC	 XML		PATH '.',
	qualifiedAircraftDoc	XML	PATH '..'
) as X
WHERE QA.QA_UUID =  V_UUID

Элемент qualifiedAircraftDoc обращается к вышестоящему значению в иерархии. Данный запрос будет выполняться эффективнее, если этот элемент переместить в выражение SELECT.

Листинг 8. Если сгенерировано несколько элементов computerId, то для повышения производительности следует использовать показанный ниже код.
INSERT INTO computerid (parentUUID, facilityID, computersystemID, idnumber, 
	computerdoc, qualifiedaircraftdoc)
SELECT QA.QA_UUID, X.facilityID, X.computersystemID, x.idnumber, 
	XMLDOCUMENT(x.computerdoc) as cdoc , 
XMLQUERY('declare namespace nxcm="http://tfm.faa.gov/tfms/NasXCommonMessages";
	$MDOC/nxcm:qualifiedAircraftId' PASSING QA.DOC as "MDOC") as qdoc
FROM qualifiedaircraft qa, XMLTABLE (
  '$DOC/nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING QA.DOC as "DOC"
  COLUMNS
	facilityID	 VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	idNumber	 VARCHAR(3)	PATH 'nxce:idNumber',
	computerDOC	 XML		PATH '.'
) as X
WHERE QA.QA_UUID =  V_UUID

Генерация нескольких XML-значений

Если в выражении COLUMNS задано несколько элементов с типом XML, это может существенно ухудшить производительность. Более эффективным вариантом может оказаться объединение этих элементов и осуществление их более полного разложения или реализация их обработки иным способом, отделенным от основного потока обработки, например, посредством помещения в очередь для последующей обработки.

Дополнительные стратегии разложения

Использование представлений

Сценарий: Описываемое приложение становится все более популярным, поэтому в него включается все больше данных. К сожалению, в систему не добавляют дисковое пространство, поэтому хранение новых данных в дополнение к XML-документам более не является возможным. Вы принимаете решение о реализации представлений "поверх" XML-документов.

В листинге 9 элемент computerId реализован как представление, основанное на таблице qualifiedAircraft из листинга 5.

Листинг 9. Реализация элемента computerId в виде представления
create view v_computerid as (
SELECT QA.QA_UUID as parentuuid, X.facilityID, X.computersystemID, x.idnumber, 
	XMLDOCUMENT(x.doc) as doc
FROM qualifiedaircraft qa, XMLTABLE (
  '$DOC/nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING QA.DOC as "DOC"
  COLUMNS
	facilityID	 VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	idNumber	 VARCHAR(3)	PATH 'nxce:idNumber',
	DOC		 XML		PATH '.'
) as X
)

Представления могут быть вложенными. Метод переноса XML позволяет построить фрагменты представления computerId на основе представления qualifiedAircraft.

Рисунок 7. Представления QualifiedAircraft и ComputerId, основанные на представлении ArrivalInformation
Schema diagram of ArrivalInformation, QalifiedAircraft, and ComputerId showing nesting of views
Листинг 10. ComputerId и qualifiedAircraftId как представления таблицы arrivalInformation
CREATE TABLE arrivalInformation (
	AI_UUID		CHAR(16) FOR BIT DATA,
	timeOfArrival	TIMESTAMP,
	timeEstimated	CHAR(1),
	DOC		XML
	);

INSERT INTO arrivalInformation (AI_UUID, timeOfArrival, timeEstimated, DOC)
SELECT generate_unique(), UDFS.ZULU_TO_TIMESTAMP(X.timeofarrival), 
      CASE UPPER(X.timeEstimated) WHEN 'TRUE' then 'T' else 'F' END, XMLDOCUMENT(X.DOC)
  FROM XMLTABLE (
    '$DOC/*:asdiOutput/*:asdiMessage/*:arrivalInformation'
	PASSING CAST(? as XML) as "DOC"
    COLUMNS
	  timeofarrival	VARCHAR(20)	PATH 'nxcm:timeOfArrival',
	  timeEstimated	VARCHAR(6)	PATH 'nxcm:timeOfArrival/@estimated',
	  DOC		XML		PATH '.'
  ) as X
;

create or replace view v_qualifiedaircraft as (
SELECT AI_UUID as parentUUID, GENERATE_UNIQUE() as QA_UUID, X.aircraftID, 
	X.flightClass, X.DOC
FROM arrivalInformation AI, XMLTABLE (
    '$DOC/*:arrivalInformation/nxcm:qualifiedAircraftId'
	PASSING AI.DOC as "DOC"
    COLUMNS
	  aircraftID	VARCHAR(6)	PATH 'nxce:aircraftId',
	  flightClass	VARCHAR(16)	PATH '@flightClass',
	  DOC		XML		PATH '.'
  ) as X
);

create or replace view v_computerid as (
 SELECT VQA.QA_UUID as parentuuid, X.facilityID, X.computersystemID, x.idnumber, 
	XMLDOCUMENT(x.doc) as computer
 FROM v_qualifiedaircraft vqa, XMLTABLE (
  '$DOC/ nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING VQA.DOC as "DOC"
  COLUMNS
	facilityID	 VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	idNumber	 VARCHAR(3)	PATH 'nxce:idNumber',
	DOC		 XML		PATH '.'
 ) as X
)
;

Представление computerId основано на представлении qualifiedAircraft, которое, в свою очередь, основано на столбце DOC таблицы arrivalInformation.

Листинг 11. Обратное связывание элементов с помощью сгенерированных UUID-идентификаторов.
select AI.timeOfArrival, AI.timeEstimated, VQA.aircraftID, VCI.facilityId
from arrivalInformation AI, v_qualifiedaircraft VQA, v_computerid VCI
WHERE VQA.parentuuid = AI.AI_UUID
  AND VCI.parentuuid = VQA.QA_UUID
Листинг 12. Функция ZULU_TO_TIMESTAMP, использованная выше для преобразования метки времени из ASDI-сообщений в метку времени DB2.
CREATE OR REPLACE FUNCTION UDFS.ZULU_TO_TIMESTAMP ( inStr VARCHAR(22) )
  RETURNS TIMESTAMP LANGUAGE SQL SPECIFIC ZULU_TO_TIMESTAMP
  DETERMINISTIC CALLED ON NULL INPUT
  
   return values (timestamp(substr(inStr,1,10) || '-' || 
   	replace(substr(inStr,12,instr(inStr,'Z')-12),':','.')))

Использование хранимых процедур

Хранимые процедуры – это метод, позволяющий разместить бизнес-логику в непосредственной близости к данным в базе данных. Такие процедуры способны выполнять сложные операции, которые трудно или невозможно реализовать с помощью SQL. В данном случае хранимая процедура может принимать один XML-документ в качестве аргумента и вставлять его фрагменты в несколько таблиц.

Листинг 13. Схема таблиц и хранимая процедура для заполнения нескольких таблиц из одного XML-документа.
CREATE TABLE arrivalInformation (
	AI_UUID		CHAR(13) FOR BIT DATA,
	timeOfArrival	TIMESTAMP,
	timeEstimated	CHAR(1),
	DOC		XML
	)@

CREATE TABLE qualifiedAircraft (
	parentuuid 	CHAR(13) FOR BIT DATA,
	QA_UUID 	CHAR(13) FOR BIT DATA,
	aircraftID	VARCHAR(6),
	flightClass	VARCHAR(16),
	DOC		XML
)
@
CREATE TABLE computerid (
	parentUUID	 CHAR(13) FOR BIT DATA,
	facilityID	 VARCHAR(4),
	computersystemID VARCHAR(5),
	idnumber	 VARCHAR(3),
	DOC		 XML
)
@


CREATE OR REPLACE PROCEDURE shred_flightPlanInfo (IN cFlightPlanInfo XML)
	SPECIFIC SHRED_FLIGHTPLANINFO
	LANGUAGE SQL
main: BEGIN

	DECLARE v_AI_uuid, v_QA_uuid	CHAR(13) for bit data;

	-- Loop over the ArrivalInformation

    FOR v_AI AS select UDFS.ZULU_TO_TIMESTAMP(X.timeofarrival) timeofarrival, 
	CASE UPPER(X.timeEstimated) WHEN 'TRUE' then 'T' else 'F' END timeestimated, 
	XMLDOCUMENT(X.DOC) DOC
	  FROM XMLTABLE (
	    '$DOC/*:asdiOutput/*:asdiMessage/*:arrivalInformation'
		PASSING cFlightPlanInfo as "DOC"
	    COLUMNS
		timeofarrival	VARCHAR(20)	PATH 'nxcm:timeOfArrival',
		timeEstimated	VARCHAR(6)	PATH 'nxcm:timeOfArrival/@estimated',
		DOC		XML		PATH '.'
		  ) as X
	DO
		SET v_AI_uuid = GENERATE_UNIQUE();
		INSERT INTO arrivalInformation (AI_UUID, timeOfArrival, 
			timeEstimated, doc) 
		  values (v_AI_uuid,
		  	  v_AI.timeOfArrival,
		  	  v_AI.timeEstimated,
		  	  v_AI.DOC );
		
		FOR v_QA AS SELECT GENERATE_UNIQUE() as QA_UUID, X.aircraftID, 
				X.flightClass, XMLDOCUMENT(X.DOC) DOC
			FROM XMLTABLE (
		'$DOC/*:arrivalInformation/nxcm:qualifiedAircraftId'
		PASSING v_AI.DOC as "DOC"
		COLUMNS
		  aircraftID	VARCHAR(6)	PATH 'nxce:aircraftId',
		  flightClass	VARCHAR(16)	PATH '@flightClass',
		  DOC		XML		PATH '.'
			  ) as X
		DO
			SET v_QA_uuid = GENERATE_UNIQUE();
			INSERT INTO qualifiedAircraft (parentUUID, QA_UUID, 
					aircraftID, flightClass, doc) 
				values (v_AI_uuid,
					v_QA_uuid,
					v_QA.aircraftID,
					v_QA.flightClass,
					v_QA.DOC );
			
		  INSERT INTO computerid (parentUUID, facilityID, computersystemid, 
		    idnumber, doc) 
		  SELECT v_QA_uuid, X.facilityID, X.computersystemID, x.idnumber, 
			XMLDOCUMENT(x.doc) as computer
		  FROM v_qualifiedaircraft vqa, XMLTABLE (
		    '$DOC/nxcm:qualifiedAircraftId/nxce:computerId'
			PASSING v_QA.DOC as "DOC"
			COLUMNS
			  facilityID	   VARCHAR(4)	PATH 'nxce:facilityIdentifier',
			  computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
			  idNumber	   VARCHAR(3)	PATH 'nxce:idNumber',
			  DOC		   XML		PATH '.'
			) as X;
		END FOR;
	END FOR;
	COMMIT;
END main 
@

Использование триггеров

Если бы ASDI-схема была полностью создана в реляционной базе данных, то было бы легко понять, когда несколько сообщений осуществляют запись в одни и те же объекты. Поскольку декомпозиция объекта осуществляется стандартным образом независимо от его источника, этот процесс может обрабатываться как набор триггеров.

Реляционные таблицы допускают задание триггеров, которые в случае вставки новой строки вызывают каскадный эффект. В настоящее время существует ограничение на использование XML-данных в переменных перехода внутри триггера, однако это ограничение можно обойти посредством присоединения к только что вставленной строке с помощью сгенерированного ID-идентфикатора в триггере AFTER.

Листинг 14. DDL-код триггера, использующего схему из хранимой процедуры.
CREATE OR REPLACE TRIGGER trig_ins_qa AFTER INSERT ON qualifiedAircraft
REFERENCING NEW as NEWROW FOR EACH ROW
  INSERT INTO computerid (parentUUID, facilityID, computersystemID, idnumber, DOC)
    SELECT NEWROW.QA_UUID, X.facilityID, X.computersystemID, x.idnumber, 
    	XMLDOCUMENT(X.DOC)
    FROM qualifiedAircraft QA, XMLTABLE (
         '$DOC/nxcm:qualifiedAircraftId/nxce:computerId'
	PASSING QA.DOC as "DOC"
	COLUMNS
	 facilityID	  VARCHAR(4)	PATH 'nxce:facilityIdentifier',
	 computerSystemId VARCHAR(5)	PATH 'nxce:computerSystemId',
	 idNumber	  VARCHAR(3)	PATH 'nxce:idNumber',
	 DOC		  XML		PATH '.'
    ) as X
    WHERE QA.QA_UUID = NEWROW.QA_UUID
;

CREATE OR REPLACE TRIGGER trig_ins_ai AFTER INSERT ON arrivalInformation
REFERENCING NEW as NEWROW FOR EACH ROW
  INSERT INTO qualifiedAircraft (parentUUID, QA_UUID, aircraftId, flightClass, DOC)
   SELECT NEWROW.AI_UUID, GENERATE_UNIQUE(), X.aircraftId, X.flightClass, 
  	XMLDOCUMENT(X.DOC)
   FROM arrivalinformation AI, XMLTABLE (
	  '$DOC/*:arrivalInformation/nxcm:qualifiedAircraftId'
	PASSING AI.DOC as "DOC"
	COLUMNS
	 aircraftID	VARCHAR(6)	PATH 'nxce:aircraftId',
	 flightClass	VARCHAR(16)	PATH '@flightClass',
	 DOC		XML		PATH '.'
    ) as X
    WHERE AI.AI_UUID = NEWROW.AI_UUID
;

insert into arrivalInformation (AI_UUID, timeOfArrival, timeEstimated, DOC)
SELECT generate_unique(), UDFS.ZULU_TO_TIMESTAMP(X.timeOfArrival), 
    CASE UPPER(X.timeEstimated) WHEN 'TRUE' then 'T' else 'F' END, 
    XMLDOCUMENT(X.DOC)
FROM XMLTABLE (
	 '$DOC/*:asdiOutput/*:asdiMessage/*:arrivalInformation'
	PASSING CAST(? as XML) as "DOC"
	COLUMNS
	  timeofarrival	VARCHAR(20)	PATH 'nxcm:timeOfArrival',
	  timeEstimated	VARCHAR(6)	PATH 'nxcm:timeOfArrival/@estimated',
	  DOC		XML		PATH '.'
) as X

Заключение

В статье описано несколько способов преобразования XML-данных в реляционные данные. Для демонстрации обращения с множественными иерархиями и повторяющимися элементами использовалась сложная, но четко определенная схема документа. Особое внимание было уделено таким моментам, как повторяющиеся элементы, реляционные иерархии и учет будущих потребностей.

Приложение A – использованный XML-документ

Листинг 15. Фрагмент учебного документа arrivalInformation, используемый для тестирования.
<?xml version="1.0" encoding="UTF-8"?><asdiOutput 
	xmlns="http://tfm.faa.gov/tfms/TFMS_XIS" 
	xmlns:nxce="http://tfm.faa.gov/tfms/NasXCoreElements"
	xmlns:mmd="http://tfm.faa.gov/tfms/MessageMetaData"
	xmlns:nxcm="http://tfm.faa.gov/tfms/NasXCommonMessages"
	xmlns:idr="http://tfm.faa.gov/tfms/TFMS_IDRS" 
	xmlns:xis="http://tfm.faa.gov/tfms/TFMS_XIS" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://tfm.faa.gov/tfms/TFMS_XIS 
	http://localhost:58489/tfms/schema/TFMS_XIS.xsd" 
	timestamp="2009-09-21T12:57:19Z">
  <asdiMessage sourceFacility="KZSE" sourceTimeStamp="2009-09-21T12:57:07Z" trigger="AZ">
    <arrivalInformation>
      <nxcm:qualifiedAircraftId>
        <nxce:aircraftId>QXE483</nxce:aircraftId>
        <nxce:computerId>
          <nxce:facilityIdentifier>KZSE</nxce:facilityIdentifier>
        </nxce:computerId>
        <nxce:departurePoint>
          <nxce:fix>
            <nxce:namedFix>KBLI</nxce:namedFix>
          </nxce:fix>
        </nxce:departurePoint>
        <nxce:arrivalPoint>
          <nxce:fix>
            <nxce:namedFix>KSEA</nxce:namedFix>
          </nxce:fix>
        </nxce:arrivalPoint>
      </nxcm:qualifiedAircraftId>
      <nxcm:timeOfArrival estimated="true">2009-09-21T12:54:00Z</nxcm:timeOfArrival>
    </arrivalInformation>
  </asdiMessage>
</asdiOutput>

Приложение B – Измененный XML-документ

Листинг 16. Тестовый документ с несколькими записями computerId для тестирования циклической логики.
<?xml version="1.0" encoding="UTF-8"?><asdiOutput 
	xmlns="http://tfm.faa.gov/tfms/TFMS_XIS" 
	xmlns:nxce="http://tfm.faa.gov/tfms/NasXCoreElements" 
	xmlns:mmd="http://tfm.faa.gov/tfms/MessageMetaData" 
	xmlns:nxcm="http://tfm.faa.gov/tfms/NasXCommonMessages" 
	xmlns:idr="http://tfm.faa.gov/tfms/TFMS_IDRS" 
	xmlns:xis="http://tfm.faa.gov/tfms/TFMS_XIS" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://tfm.faa.gov/tfms/TFMS_XIS 
	http://localhost:58489/tfms/schema/TFMS_XIS.xsd" 
	timestamp="2009-09-21T12:57:19Z">
  <asdiMessage sourceFacility="KZSE" 
  	sourceTimeStamp="2009-09-21T12:57:07Z" trigger="AZ">
    <arrivalInformation>
      <nxcm:qualifiedAircraftId>
        <nxce:aircraftId>QXE483</nxce:aircraftId>
        <nxce:computerId>
          <nxce:facilityIdentifier>KZSE</nxce:facilityIdentifier>
        </nxce:computerId>
        <nxce:computerId>
          <nxce:facilityIdentifier>RGW1</nxce:facilityIdentifier>
        </nxce:computerId>
        <nxce:computerId>
          <nxce:facilityIdentifier>JAW1</nxce:facilityIdentifier>
        </nxce:computerId>
        <nxce:departurePoint>
          <nxce:fix>
            <nxce:namedFix>KBLI</nxce:namedFix>
          </nxce:fix>
        </nxce:departurePoint>
        <nxce:arrivalPoint>
          <nxce:fix>
            <nxce:namedFix>KSEA</nxce:namedFix>
          </nxce:fix>
        </nxce:arrivalPoint>
      </nxcm:qualifiedAircraftId>
      <nxcm:timeOfArrival 
      	estimated="true">2009-09-21T12:54:00Z</nxcm:timeOfArrival>
    </arrivalInformation>
  </asdiMessage>
</asdiOutput>

Ресурсы

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management, XML
ArticleID=936220
ArticleTitle=Разложение XML-данных в DB2 с помощью технологии pureXML и функции XMLTable
publish-date=07012013