SQL Server: вывод поля XML в виде табличных данных с помощью хранимой процедуры

Я использую таблицу с полем данных XML для хранения журналов аудита всех других таблиц в базе данных.

Это означает, что одно и то же поле XML содержит различную информацию XML. Например, в моей таблице есть две записи с XML-данными, например:

1я запись:

<client>
  <name>xyz</name>
  <ssn>432-54-4231</ssn>
</client>

2ая запись:

<emp>
  <name>abc</name>
  <sal>5000</sal>
</emp>

Это два примера форматов и всего две записи. На самом деле таблица имеет гораздо больше форматов XML в одном и том же поле и множество записей в каждом формате.

Теперь моя проблема заключается в том, что по запросу мне нужно, чтобы эти форматы XML были преобразованы в табличные наборы результатов.

Какие есть варианты для меня? Было бы обычной задачей запрашивать эту таблицу и создавать из нее отчеты. Я хочу создать хранимую процедуру, которой я могу передать, что мне нужно запросить «<emp>» или «<client>», тогда моя хранимая процедура должна возвращать табличные данные.


xml sql-server stored-procedures sqlxml
person Community    schedule 15.06.2009    source источник
comment
вы спрашиваете, как разделить XML известного набора схем на таблицы или как определить схему и создать соответствующую таблицу на лету? Кроме того, SQL 2K или SQL 2K5/2K8?   -  person Remus Rusanu    schedule 15.06.2009

Ответы (4)


arrow_upward
1
arrow_downward

это помогает?

INSERT INTO @t (data) SELECT '
<client>
  <name>xyz</name>
  <ssn>432-54-4231</ssn>
</client>'

INSERT INTO @t (data) SELECT '
<emp>
  <name>abc</name>
  <sal>5000</sal>
</emp>'
DECLARE @el VARCHAR(20)

SELECT @el = 'client'

SELECT
    x.value('local-name(.)', 'VARCHAR(20)') AS ColumnName,
    x.value('.','VARCHAR(20)') AS ColumnValue
FROM @t
CROSS APPLY data.nodes('/*[local-name(.)=sql:variable("@el")]') a (x)
/*
ColumnName           ColumnValue
-------------------- --------------------
client               xyz432-54-4231
*/

SELECT @el = 'emp'
SELECT
    x.value('local-name(.)', 'VARCHAR(20)') AS ColumnName,
    x.value('.','VARCHAR(20)') AS ColumnValue
FROM @t
CROSS APPLY data.nodes('/*[local-name(.)=sql:variable("@el")]') a (x)
/*
ColumnName           ColumnValue
-------------------- --------------------
emp                  abc5000
*/
person jacob sebastian    schedule 28.04.2010

arrow_upward
0
arrow_downward

Ни xyz432-54-4231, ни abc5000 не являются допустимым XML.

Вы можете попытаться выбрать только один конкретный формат с подобным оператором, например:

select * 
from YourTable 
where YourColumn like '[a-z][a-z][a-z][0-9][0-9][0-9][0-9]'

Это будет соответствовать 3 буквам, за которыми следуют 4 цифры.

Лучшим вариантом, вероятно, является добавление в таблицу дополнительного столбца, в котором вы сохраняете тип ведения журнала. Затем вы можете использовать этот столбец для выбора всех строк «emp» или «client».

person Andomar    schedule 15.06.2009
comment
Тем временем вопрос был отредактирован, вы видели неформатированную версию. - person Tomalak; 15.06.2009

arrow_upward
0
arrow_downward

Вариантом может быть создание серии представлений, представляющих таблицу aduit, для каждого типа отношений, которые вы выполняете, например.

select
    c.value('name','nvarchar(50)') as name,
    c.value('ssn', 'nvarchar(20)') as ssn
from yourtable
cross apply yourxmlcolumn.nodes('/client') as t(c)

затем вы можете следовать тому же шаблону для emp

вы также можете создать представление (или вычисляемый столбец) для идентификации каждого типа xml следующим образом:

select yourxmlcolumn.value('local-name(/*[1])', 'varchar(100)') as objectType
from yourtable
person Ralph Shillington    schedule 15.06.2009

arrow_upward
0
arrow_downward

Используйте открытый метод xml

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc ВЫВОД, @xmldoc

SELECT * в #test
FROM OPENXML (@idoc, 'xmlfilepath',2)
WITH (Name varchar(50),ssn varchar(20)
)

EXEC sp_xml_removedocument @idoc

после того, как вы получите данные в #test, и вы сможете манипулировать этим.

вы можете поместить данные diff в файл diff xml.

person KuldipMCA    schedule 30.06.2009