Использование XQuery в Sql Server для анализа сложных типов XML

#sql #sql-server-2005 #sql-server-2008

Вопрос:

У меня есть следующий XML :

 <Feed>
  <FeedId>10</FeedId>
   <Component>
     <Date>2011-10-01</Date>
     <Date>2011-10-02</Date>
   </Component>
</Feed>

Теперь, если возможно, я хотел бы проанализировать XML в sql, чтобы он сериализовался в следующие реляционные данные:

 FeedId   Component_Date
10       2011-10-01
10       2011-10-02

Однако, используя следующий SQL:

 DECLARE @XML XML;
DECLARE @XMLNodes XML;
SET @XML = '<Feed><FeedId>10</FeedId><Component><Date>2011-10-01</Date><Date>2011-10-02</Date></Component></Feed>';

SELECT  t.a.query('FeedId').value('.', 'INT') AS FeedId
    ,t.a.query('Component/Date').value('.', 'VARCHAR(80)') AS [Component_Date]
    FROM @XML.nodes(' /Feed') AS t(a)

Самое близкое, что я получаю, это :

 FeedId  Component_Date
10  2011-10-012011-10-02

Итак, значения даты отображаются в одной строке, возможно ли достичь того, чего я хочу, с помощью XQuery?

Ответ №1:

Вам нужен второй вызов .nodes() , поскольку в вашем XML есть несколько записей — попробуйте это:

 SELECT  
    t.a.value('(FeedId)[1]', 'INT') AS FeedId,
    c.d.value('(.)[1]', 'DATETIME') AS [Component_Date]
FROM 
    @XML.nodes('/Feed') AS t(a)
CROSS APPLY
    t.a.nodes('Component/Date') AS C(D)

Дает мне вывод:

 FeedId  Component_Date
  10    2011-10-01 00:00:00.000
  10    2011-10-02 00:00:00.000

1.На самом деле нет необходимости использовать второй nodes(). Вы можете получить значение FeedId с помощью, '../../FeedId[1] но это намного быстрее. 1

2. Отлично, именно то, что я хотел, Микаэль, спасибо

Ответ №2:

Хорошо, я могу сделать это с помощью метода OPENXML:

 eclare @idoc int
DECLARE @XML XML;
DECLARE @XMLNodes XML;
SET @XML = '<Feed><FeedId>10</FeedId><Component><Date>2011-10-01</Date><Date>2011-10-02</Date></Component></Feed>';

exec sp_xml_preparedocument @idoc OUTPUT, @XML
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/Feed/Component/Date',1)
         WITH (
                FeedId              Int         '../../FeedId',
                ComponentDate       Date         'text()'
               )