SQL Server извлекает все значения

У меня есть таблица, назовем ее TBL, со столбцом типа XML.

Столбец XML (названный здесь xml) имеет формат:

<START xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="87jjhanM">
    <Header xmlns="">
    ...
    </Header>
    <Fetch xmlns="">
    .....
    </Fetch>
    <Send xmlns="">
        <Supplier>
            <Deals>
                <Deal>
                   <Field1> </Field1>
                   <Field2> </Field2>
                </Deal>
            </Deals>
        </Supplier>
        <Supplier>
            <Deals>
                <Deal>
                   <Field1> </Field1>
                   <Field2> </Field2>
                </Deal>
            </Deals>
        </Supplier>
    </Send>
</START>

Обратите внимание, что каждый документ XML может иметь несколько тегов <Supplier>. Меня интересует получение значений Field1 и Field2.

Из чтения, например, Как я могу запросить XML-столбец SQL Server и вернуть все значения для определенного узла? и Получение нескольких записей из столбца xml с помощью value() в SQL Server кажется, что я должен использовать какое-то перекрестное применение.

Я просто не могу заставить его работать. С value() я вполне способен получить первый найденный экземпляр, но с nodes() я терплю неудачу (SQL Server).

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

SELECT  
    xml.value('(/Supplier/Deals/Deal/Field1[1])[1]', 'VARCHAR(100)') AS A
FROM    
    TBL
CROSS APPLY 
    xml.nodes('/Start/Send') x(A);

который, насколько я понимаю, просто ищет первое значение /Supplier/Deals/Deal/Field1 под каждым /Start/Send/, но это не работает - он просто возвращает 0 затронутых строк (я знаю, что конкретная структура существует в файлах XML, я должен сказать).

Я уверен, что это довольно просто, но я просто не могу понять, почему это не работает. Любая помощь приветствуется.


xml nodes sql-server cross-apply
person user1938803    schedule 12.02.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Довольно много ошибок, которые, вероятно, не имеют прямого отношения к вашему вопросу: XML чувствителен к регистру (поэтому START, а не Start), внешний элемент находится в пространстве имен (87jjhanM) и поэтому требует WITH XMLNAMESPACES, такого рода вещи. Но если мы со всем этим разберемся:

WITH XMLNAMESPACES ('87jjhanM' AS n)
SELECT 
    A.value('Field1[1]', 'VARCHAR(100)') AS Field1, 
    A.value('Field2[1]', 'VARCHAR(100)') AS Field2
FROM    TBL
CROSS APPLY [xml].nodes('/n:START/Send/Supplier/Deals/Deal') x(A);

В идеале CROSS APPLY следует выполнять для интересующего нас самого нижнего повторяющегося элемента (в данном случае Deal), чтобы максимально упростить запросы value, но это не является жестким требованием.

Если вместо этого вам нужны все значения в Field1 и Field2, но вам все равно, в каком поле они на самом деле находятся, вы можете сопоставить их по имени:

WITH XMLNAMESPACES ('87jjhanM' AS n)
SELECT 
    A.value('.', 'VARCHAR(100)') AS [Field]
FROM    TBL
CROSS APPLY [xml].nodes('/n:START/Send/Supplier/Deals/Deal/*[local-name()="Field1" or local-name()="Field2"]') x(A);

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

person Jeroen Mostert    schedule 12.02.2018

arrow_upward
2
arrow_downward

Вы попытались сократить и очистить это для краткости, это хорошо. Но, по крайней мере, я так думаю, ты слишком много сделал...

Йерун Мостерт уже указал на регистр ("START"!="Start") и пространства имен. В первой строке есть (довольно странное) пространство имен по умолчанию, а некоторые пространства имен вы вообще не используете.

Повторяющиеся xmlns="" в вашем XML довольно опасны. Это, вероятно, создано командой T-SQL с FOR XML и подзапросами. Дело в том, что это не просто глупо игнорировать, но вы определяете новое пространство имен по умолчанию для внутренних элементов. Вот почему я использовал подстановочный знак пространства имен *: и опустил объявление пространства имен.

Я добавил дополнительные элементы на каждый уровень и предположил, что ниже <Deals> может быть 1:n <Supplier> и 1:n <Deal> узлов (по крайней мере, название указывает на это). Для этого вы можете использовать два уровня OUTER / CROSS APPLY:

DECLARE @mockupTBL TABLE(ID INT IDENTITY, TheXml XML);
INSERT INTO @mockupTBL VALUES
(N'<START xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="87jjhanM">
   <Header xmlns="">
      <SomeNodeWithinHeader SomeValue="blah"/>
   </Header>
 <Fetch xmlns="">
      <SomeNodeWithinFetch SomeValue="blubb"/>
 </Fetch>
 <Send xmlns="">
   <Supplier>
     <SupplierRelatedData value="Sup1"/>
     <Deals>
       <Deal>
         <Field1>A1</Field1>
         <Field2>A2</Field2>
       </Deal>
       <Deal>
         <Field1>A3</Field1>
         <Field2>A4</Field2>
       </Deal>
     </Deals>
   </Supplier>
   <Supplier>
     <SupplierRelatedData value="Sup2"/>
     <Deals>
       <Deal>
         <Field1>B1</Field1>
         <Field2>B2</Field2>
       </Deal>
     </Deals>
   </Supplier>
 </Send>
 </START>');

 SELECT m.TheXml.value(N'(/*:START/Header/SomeNodeWithinHeader/@SomeValue)[1]',N'nvarchar(max)') ValueWithinHeader
       ,m.TheXml.value(N'(/*:START/Fetch/SomeNodeWithinFetch/@SomeValue)[1]',N'nvarchar(max)') ValueWithinFetch
       ,sup.value(N'(SupplierRelatedData/@value)[1]',N'nvarchar(max)') SupplierRelatedData
       ,deal.value(N'(Field1/text())[1]',N'nvarchar(max)') AS Field1
       ,deal.value(N'(Field2/text())[1]',N'nvarchar(max)') AS Field2
 FROM @mockupTBL AS m
 OUTER APPLY m.TheXml.nodes(N'/*:START/Send/Supplier') AS A(sup)
 OUTER APPLY A.sup.nodes(N'Deals/Deal') AS B(deal)

Результат

blah    blubb   Sup1    A1  A2
blah    blubb   Sup1    A3  A4
blah    blubb   Sup2    B1  B2
person Shnugo    schedule 12.02.2018