Чтение повторяющихся тегов XML на сервере sql

 Declare @MainXml XML = 
 '<?xml version="1.0" encoding="utf-8"?>
 <result>
 <cash number="10">
 <account amt="11.00" status="Closed"/>
 <account amt="12.00" status="Closed"/>                       
 </cash>        
 <cash number="20">
 <account amt="21.00" status="Closed"/>
 <account amt="22.00" status="Closed"/>                       
 </cash>        
 </result>'

Я читаю данные, используя следующий запрос

Declare @Innerxml xml; 
SELECT @Innerxml = T.c.query('<result>{/result/cash}</result>')
FROM   @MainXml.nodes('result') T(c)

SELECT  
Result.Claim.value('(./@number)[1]','varchar(max)') as C1,
Result.Claim.value('(./@amt)[1]','varchar(max)') as C2,
Result.Claim.value('(./@status)[1]','varchar(max)') as C3   
From @Innerxml.nodes('/result/cash/account') Result(Claim)

Я хочу прочитать xml и сохранить в БД, как показано ниже.

C1   C2     C3
----------------
10   11.00  Closed
10   12.00  Closed
20   21.00  Closed
20   22.00  Closed

но мой запрос возвращает только NULL в столбце C1. Пожалуйста, помогите мне здесь. заранее спасибо


xml sql-server sql-server-2008 xml-parsing sqlxml
person user1893874    schedule 23.04.2015    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Вы не должны использовать родительскую ось в запросах XML в SQL Server. Созданный план запроса будет O(n2). Для каждого узла в XML проверяются все узлы в XML.

Сначала измельчите result/cash, а затем измельчите account перекрестным способом.

select C.X.value('@number', 'varchar(max)') as C1,
       A.X.value('@amt', 'varchar(max)') as C2,
       A.X.value('@status', 'varchar(max)') as C3
from @MainXml.nodes('result/cash') as C(X)
  cross apply C.X.nodes('account') as A(X)

Я не вижу смысла создавать вторую XML-переменную. Используйте @MainXML напрямую.

person Mikael Eriksson    schedule 23.04.2015
comment
Хотя это звучит логично, я не уверен, что это всегда так. Думаю, я видел, что запросы с .. иногда выполнялись лучше, чем запросы с несколькими подобными применениями, но на данный момент у меня нет точного примера. Вот и было бы интересно протестировать - person Roman Pekar; 23.04.2015
comment
Также я не согласен с тем, что план будет O(n^2), он должен быть линейным, потому что у каждого узла есть только один родитель. - person Roman Pekar; 23.04.2015
comment
@RomanPekar взгляните на stackoverflow.com/questions/24196516/ - person Mikael Eriksson; 23.04.2015
comment
спасибо, хороший ответ! Как я уже сказал, вполне логично, что лучше измельчать xml, как вы сделали здесь, и это всегда моя первая попытка, я просто помню, что я видел ситуации, когда родительский узел работает лучше, но опять же, пока нет примера. - person Roman Pekar; 23.04.2015
comment
@RomanPekar, возможно, у вас был XML-индекс? План отличается, если вы это сделаете. - person Mikael Eriksson; 23.04.2015
comment
нет, я думаю, что однажды видел ответ на SO, где родительские узлы работали лучше, и это также мог быть сильно вложенный xml, который я использовал для работы. Я постараюсь проверить, смогу ли я создать пример - person Roman Pekar; 23.04.2015
comment
Спасибо Микаэлю за подробное обсуждение - person user1893874; 23.04.2015

arrow_upward
2
arrow_downward

@number является атрибутом <cash>, но ваш узел контекста - <account>. Вам нужно подняться на один уровень вверх по дереву XML, чтобы добраться до узла <cash> перед доступом к атрибуту. Вы можете сделать .., чтобы добраться до родителя текущего узла в xpath:

SELECT  
Result.Claim.value('(../@number)[1]','varchar(max)') as C1,
Result.Claim.value('(./@amt)[1]','varchar(max)') as C2,
Result.Claim.value('(./@status)[1]','varchar(max)') as C3   
From @Innerxml.nodes('/result/cash/account') Result(Claim)
person har07    schedule 23.04.2015