Как удалить избыточное пространство имен во вложенном запросе при использовании FOR XML PATH
ОБНОВЛЕНИЕ: я обнаружил, что для этой проблемы поднят элемент Microsoft Connect здесь
При использовании FOR XML PATH
и WITH XMLNAMESPACES
для объявления пространства имен по умолчанию, я получу дублирование декларации пространства имен в любых узлах верхнего уровня для вложенных запросов, использующих FOR XML, я наткнулся на несколько решений онлайн, но я ' я не совсем убежден...
Вот полный пример
/*
drop table t1
drop table t2
*/
create table t1 ( c1 int, c2 varchar(50))
create table t2 ( c1 int, c2 int, c3 varchar(50))
insert t1 values
(1, 'Mouse'),
(2, 'Chicken'),
(3, 'Snake');
insert t2 values
(1, 1, 'Front Right'),
(2, 1, 'Front Left'),
(3, 1, 'Back Right'),
(4, 1, 'Back Left'),
(5, 2, 'Right'),
(6, 2, 'Left')
;with XmlNamespaces( default 'uri:animal')
select
a.c2 as "@species"
, (select l.c3 as "text()"
from t2 l where l.c2 = a.c1
for xml path('leg'), type) as "legs"
from t1 a
for xml path('animal'), root('zoo')
Какое лучшее решение?
Ответы
Ответ 1
Если я правильно понял, вы имеете в виду поведение, которое вы можете увидеть в запросе типа:
DECLARE @Order TABLE (
OrderID INT,
OrderDate DATETIME)
DECLARE @OrderDetail TABLE (
OrderID INT,
ItemID VARCHAR(1),
ItemName VARCHAR(50),
Qty INT)
INSERT @Order
VALUES
(1, '2010-01-01'),
(2, '2010-01-02')
INSERT @OrderDetail
VALUES
(1, 'A', 'Drink', 5),
(1, 'B', 'Cup', 2),
(2, 'A', 'Drink', 2),
(2, 'C', 'Straw', 1),
(2, 'D', 'Napkin', 1)
;WITH XMLNAMESPACES('http://test.com/order' AS od)
SELECT
OrderID AS "@OrderID",
(SELECT
ItemID AS "@od:ItemID",
ItemName AS "data()"
FROM @OrderDetail
WHERE OrderID = o.OrderID
FOR XML PATH ('od.Item'), TYPE)
FROM @Order o
FOR XML PATH ('od.Order'), TYPE, ROOT('xml')
Что дает следующие результаты:
<xml xmlns:od="http://test.com/order">
<od.Order OrderID="1">
<od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="B">Cup</od.Item>
</od.Order>
<od.Order OrderID="2">
<od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="C">Straw</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="D">Napkin</od.Item>
</od.Order>
</xml>
Как вы сказали, пространство имен повторяется в результатах подзапросов.
Это поведение является особенностью в соответствии с беседой на devnetnewsgroup (веб-сайт теперь не функционирует), хотя есть возможность голосовать при его изменении.
Мое предлагаемое решение - вернуться к FOR XML EXPLICIT
:
SELECT
1 AS Tag,
NULL AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
NULL AS [od:Order!2!OrderID],
NULL AS [od:Item!3],
NULL AS [od:Item!3!ItemID]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
OrderID AS [od:Order!2!OrderID],
NULL AS [od:Item!3],
NULL [od:Item!3!ItemID]
FROM @Order
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
o.OrderID AS [od:Order!2!OrderID],
d.ItemName AS [od:Item!3],
d.ItemID AS [od:Item!3!ItemID]
FROM @Order o INNER JOIN @OrderDetail d ON o.OrderID = d.OrderID
ORDER BY [od:Order!2!OrderID], [od:Item!3!ItemID]
FOR XML EXPLICIT
И посмотрите следующие результаты:
<xml xmlns:od="http://test.com/order">
<od:Order OrderID="1">
<od:Item ItemID="A">Drink</od:Item>
<od:Item ItemID="B">Cup</od:Item>
</od:Order>
<od:Order OrderID="2">
<od:Item ItemID="A">Drink</od:Item>
<od:Item ItemID="C">Straw</od:Item>
<od:Item ItemID="D">Napkin</od:Item>
</od:Order>
</xml>
Ответ 2
После нескольких часов отчаяния и сотен проб и ошибок я нашел решение, приведенное ниже.
У меня была такая же проблема, когда я хотел только один xmlns
атрибут, только на корневом узле. Но у меня также был очень сложный запрос с большим количеством подзапросов, и один метод FOR XML EXPLICIT
был слишком громоздким. Так что да, я хотел удобство FOR XML PATH
в подзапросах, а также установить свои собственные xmlns
.
Я любезно позаимствовал код ответа 8kb, потому что это было так мило. Я немного подправил его для лучшего понимания. Вот код:
DECLARE @Order TABLE (OrderID INT, OrderDate DATETIME)
DECLARE @OrderDetail TABLE (OrderID INT, ItemID VARCHAR(1), Name VARCHAR(50), Qty INT)
INSERT @Order VALUES (1, '2010-01-01'), (2, '2010-01-02')
INSERT @OrderDetail VALUES (1, 'A', 'Drink', 5),
(1, 'B', 'Cup', 2),
(2, 'A', 'Drink', 2),
(2, 'C', 'Straw', 1),
(2, 'D', 'Napkin', 1)
-- Your ordinary FOR XML PATH query
DECLARE @xml XML = (SELECT OrderID AS "@OrderID",
(SELECT ItemID AS "@ItemID",
Name AS "data()"
FROM @OrderDetail
WHERE OrderID = o.OrderID
FOR XML PATH ('Item'), TYPE)
FROM @Order o
FOR XML PATH ('Order'), ROOT('dummyTag'), TYPE)
-- Magic happens here!
SELECT 1 AS Tag
,NULL AS Parent
,@xml AS [xml!1!!xmltext]
,'http://test.com/order' AS [xml!1!xmlns]
FOR XML EXPLICIT
Результат:
<xml xmlns="http://test.com/order">
<Order OrderID="1">
<Item ItemID="A">Drink</Item>
<Item ItemID="B">Cup</Item>
</Order>
<Order OrderID="2">
<Item ItemID="A">Drink</Item>
<Item ItemID="C">Straw</Item>
<Item ItemID="D">Napkin</Item>
</Order>
</xml>
Если вы выбрали @xml
только @xml
, вы увидите, что он содержит корневой узел dummyTag
. Нам это не нужно, поэтому мы удаляем его с помощью директивы xmltext
в xmltext
FOR XML EXPLICIT
:
,@xml AS [xml!1!!xmltext]
Хотя объяснение в MSDN звучит более изощренно, но практически оно говорит парсеру выбирать содержимое корневого узла XML
.
Не уверен, насколько быстрым является запрос, но сейчас я расслабляюсь и пью скотч, как джентльмен, мирно глядя на код...
Ответ 3
Альтернативное решение, которое я видел, это добавить объявление XMLNAMESPACES
после создания xml во временную переменную:
declare @xml as xml;
select @xml = (
select
a.c2 as "@species"
, (select l.c3 as "text()"
from t2 l where l.c2 = a.c1
for xml path('leg'), type) as "legs"
from t1 a
for xml path('animal'))
;with XmlNamespaces( 'uri:animal' as an)
select @xml for xml path('') , root('zoo');
Ответ 4
Проблема здесь усугубляется тем фактом, что вы не можете напрямую объявлять пространства имен вручную при использовании XML PATH. SQL Server откажется от любых имен атрибутов, начинающихся с "xmlns" и любых имен тегов с двоеточиями в них.
Вместо того, чтобы прибегать к использованию относительно недружественного XML EXPLICIT, я столкнулся с проблемой, сначала создав XML с помощью "скрытых" определений пространства имен и ссылок, затем выполнив замену строк следующим образом:
DECLARE @Order TABLE (
OrderID INT,
OrderDate DATETIME)
DECLARE @OrderDetail TABLE (
OrderID INT,
ItemID VARCHAR(1),
ItemName VARCHAR(50),
Qty INT)
INSERT @Order
VALUES
(1, '2010-01-01'),
(2, '2010-01-02')
INSERT @OrderDetail
VALUES
(1, 'A', 'Drink', 5),
(1, 'B', 'Cup', 2),
(2, 'A', 'Drink', 2),
(2, 'C', 'Straw', 1),
(2, 'D', 'Napkin', 1)
declare @xml xml
set @xml = (SELECT
'http://test.com/order' as "@xxmlns..od", -- 'Cloaked' namespace def
(SELECT OrderID AS "@OrderID",
(SELECT
ItemID AS "@od..ItemID",
ItemName AS "data()"
FROM @OrderDetail
WHERE OrderID = o.OrderID
FOR XML PATH ('od..Item'), TYPE)
FROM @Order o
FOR XML PATH ('od..Order'), TYPE)
FOR XML PATH('xml'))
set @xml = cast(replace(replace(cast(@xml as nvarchar(max)), 'xxmlns', 'xmlns'),'..',':') as xml)
select @xml
Несколько замечаний:
-
Я использую "xxmlns" в качестве моей скрытой версии "xmlns" и "..", чтобы встать на ":". Это может не сработать для вас, если вы, вероятно, будете иметь ".." как часть текстовых значений - вы можете заменить это на что-то еще, пока вы выбираете что-то, что делает допустимым XML-идентификатор.
-
Поскольку нам нужно определение xmlns на верхнем уровне, мы не можем использовать параметр ROOT для XML PATH - вместо этого мне нужно было добавить другой внешний уровень в структуру подзаголовка, чтобы достичь этого.
Ответ 5
Я немного запутываю все эти объяснения, объявляя "xmlns: animals" вручную:
Вот пример, который я написал для генерации метаданных Open Graph
DECLARE @l_xml as XML;
SELECT @l_xml =
(
SELECT 'http://ogp.me/ns# fb: http://ogp.me/ns/fb# scanilike: http://ogp.me/ns/fb/scanilike#' as 'xmlns:og',
(SELECT
(SELECT 'og:title' as 'property', title as 'content' for xml raw('meta'), TYPE),
(SELECT 'og:type' as 'property', OpenGraphWebMetadataTypes.name as 'content' for xml raw('meta'), TYPE),
(SELECT 'og:image' as 'property', image as 'content' for xml raw('meta'), TYPE),
(SELECT 'og:url' as 'property', url as 'content' for xml raw('meta'), TYPE),
(SELECT 'og:description' as 'property', description as 'content' for xml raw('meta'), TYPE),
(SELECT 'og:site_name' as 'property', siteName as 'content' for xml raw('meta'), TYPE),
(SELECT 'og:appId' as 'property', appId as 'content' for xml raw('meta'), TYPE)
FROM OpenGraphWebMetaDatas INNER JOIN OpenGraphWebMetadataTypes ON OpenGraphWebMetaDatas.type = OpenGraphWebMetadataTypes.id WHERE THING_KEY = @p_index
for xml path('header'), TYPE),
(SELECT '' as 'body' for xml path(''), TYPE)
for xml raw('html'), TYPE
)
RETURN @l_xml
возвращает ожидаемый результат
<html xmlns:og="http://ogp.me/ns# fb: http://ogp.me/ns/fb# scanilike: http://ogp.me/ns/fb/scanilike#">
<header>
<meta property="og:title" content="The First object"/>
<meta property="og:type" content="scanilike:tag"/>
<meta property="og:image" content="http://www.mygeolive.com/images/facebook/facebook-logo.jpg"/>
<meta property="og:url" content="http://www.scanilike.com/opengraph?id=1"/>
<meta property="og:description" content="This is the very first object created using the IOThing & ScanILike software. We keep it in file for history purpose. "/>
<meta property="og:site_name" content="http://www.scanilike.com"/>
<meta property="og:appId" content="200270673369521"/>
</header>
<body/>
</html>
надеюсь, что это поможет людям искать в Интернете аналогичную проблему.; -)
Ответ 6
Было бы здорово, если бы FOR XML PATH действительно работал более чисто. Переработка вашего исходного примера с помощью переменных @table:
declare @t1 table (c1 int, c2 varchar(50));
declare @t2 table (c1 int, c2 int, c3 varchar(50));
insert @t1 values
(1, 'Mouse'),
(2, 'Chicken'),
(3, 'Snake');
insert @t2 values
(1, 1, 'Front Right'),
(2, 1, 'Front Left'),
(3, 1, 'Back Right'),
(4, 1, 'Back Left'),
(5, 2, 'Right'),
(6, 2, 'Left');
;with xmlnamespaces( default 'uri:animal')
select a.c2 as "@species",
(
select l.c3 as "text()"
from @t2 l
where l.c2 = a.c1
for xml path('leg'), type
) as "legs"
from @t1 a
for xml path('animal'), root('zoo');
Получает проблемный XML с повторными объявлениями пространства имен:
<zoo xmlns="uri:animal">
<animal species="Mouse">
<legs>
<leg xmlns="uri:animal">Front Right</leg>
<leg xmlns="uri:animal">Front Left</leg>
<leg xmlns="uri:animal">Back Right</leg>
<leg xmlns="uri:animal">Back Left</leg>
</legs>
</animal>
<animal species="Chicken">
<legs>
<leg xmlns="uri:animal">Right</leg>
<leg xmlns="uri:animal">Left</leg>
</legs>
</animal>
<animal species="Snake" />
</zoo>
Вы можете переносить элементы между пространствами имен, используя XQuery с подстановочным соответствием имен (то есть *: elementName), как показано ниже, но это может быть довольно громоздким для сложного XML:
;with xmlnamespaces( default 'http://tempuri.org/this/namespace/is/meaningless' )
select (
select a.c2 as "@species",
(
select l.c3 as "text()"
from @t2 l
where l.c2 = a.c1
for xml path('leg'), type
) as "legs"
from @t1 a
for xml path('animal'), root('zoo'), type
).query('declare default element namespace "uri:animal";
<zoo>
{ for $a in *:zoo/*:animal return
<animal>
{attribute species {$a/@species}}
{ for $l in $a/*:legs return
<legs>
{ for $m in $l/*:leg return
<leg>{ $m/text() }</leg>
}</legs>
}</animal>
}</zoo>');
Что дает желаемый результат:
<zoo xmlns="uri:animal">
<animal species="Mouse">
<legs>
<leg>Front Right</leg>
<leg>Front Left</leg>
<leg>Back Right</leg>
<leg>Back Left</leg>
</legs>
</animal>
<animal species="Chicken">
<legs>
<leg>Right</leg>
<leg>Left</leg>
</legs>
</animal>
<animal species="Snake" />
</zoo>