Как запрашивать значения из узлов xml?

у меня есть таблица, содержащая столбец XML:

CREATE TABLE Batches( 
   BatchID int,
   RawXml xml 
)

Xml содержит такие элементы, как:

<GrobReportXmlFileXmlFile>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>1</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>2</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>3</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>4</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>

What i want is to generate a set, that contains:

OrganizationReportReferenceNumber  OrganizationNumber
=================================  ==================
1                                  4
2                                  4
3                                  4
4                                  4

я пробовал:

SELECT 
    foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
    foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
FROM CDRBatches.RawXML.query('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') foo

но это не работает. я пробовал:

SELECT 
    foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
    foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
FROM RawXML.nodes('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') bar(foo)

Но это не работает. Выражение XPath

/GrobReportXmlFileXmlFile/GrobReport/ReportHeader

верно; в любой другой системе xml он возвращает:

<ReportHeader>
    <OrganizationReportReferenceIdentifier>1</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>
<ReportHeader>
    <OrganizationReportReferenceIdentifier>2</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>
<ReportHeader>
    <OrganizationReportReferenceIdentifier>3</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>
<ReportHeader>
    <OrganizationReportReferenceIdentifier>4</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>

Итак, из моих запросов очевидно, что я бы хотел увидеть. Прочитав дюжину вопросов и ответов по Stackover, я не приблизился к решению проблемы.


sql-server xpath sql-server-2005 sqlxml
person Ian Boyd    schedule 05.02.2013    source источник

Ответы (4)


arrow_upward
39
arrow_downward 

SELECT  b.BatchID,
        x.XmlCol.value('(ReportHeader/OrganizationReportReferenceIdentifier)[1]','VARCHAR(100)') AS OrganizationReportReferenceIdentifier,
        x.XmlCol.value('(ReportHeader/OrganizationNumber)[1]','VARCHAR(100)') AS OrganizationNumber
FROM    Batches b
CROSS APPLY b.RawXml.nodes('/CasinoDisbursementReportXmlFile/CasinoDisbursementReport') x(XmlCol);

Демонстрация: SQLFiddle

person Bogdan Sahlean    schedule 05.02.2013
comment
Это работает; не знаю как, но работает. Что x(XmlCol) делает? Я предполагаю, что x - произвольный идентификатор, и я предполагаю, что XmlCol - произвольный идентификатор. Что происходит в этом запросе? И я предполагаю, что CROSS APPLY является синонимом FULL OUTER JOIN ON 1=1, поэтому таблица присоединяется к самой себе. Невозможно запросить значения без дополнительного соединения? - person Ian Boyd; 05.02.2013
comment
Синтаксис для nodes() вызовов: _2 _._ 3_ - это псевдоним для nodes(...) вызова метода (ссылка ). XmlCol - это псевдоним столбца xml, созданного методом nodes(). nodes() метод извлечет все /CasinoDisbursementReportXmlFile/CasinoDisbursementReport элементы (псевдоним x). CROSS APPLY (ссылка) используется для вызвать функцию / метод / запрос (в данном случае nodes()) для каждой строки с левой стороны (Batches) APPLY. - person Bogdan Sahlean; 05.02.2013
comment
CROSS APPLY (как-то) похож на INNER JOIN (а OUTER APPLY похож на LEFT OUTER JOIN). Ответ на последний вопрос: нет. Нет другого более простого решения. - person Bogdan Sahlean; 05.02.2013

arrow_upward
7
arrow_downward

Это работает, проверено ...

SELECT  n.c.value('OrganizationReportReferenceIdentifier[1]','varchar(128)') AS 'OrganizationReportReferenceNumber',  
        n.c.value('(OrganizationNumber)[1]','varchar(128)') AS 'OrganizationNumber'
FROM    Batches t
Cross   Apply RawXML.nodes('/GrobXmlFile/Grob/ReportHeader') n(c)
person Eric J. Price    schedule 05.02.2013
comment
Синтаксическая ошибка где-то около test. Но, не имея возможности разобраться в синтаксисе, я не могу предложить какое-либо исправление. - person Ian Boyd; 05.02.2013
comment
Ба, извините, это было имя моего тестового столбца, в вашем случае это должно быть RawXML. - person Eric J. Price; 05.02.2013

arrow_upward
1
arrow_downward

Попробуй это:

SELECT RawXML.value('(/GrobXmlFile//Grob//ReportHeader//OrganizationReportReferenceIdentifier/node())[1]','varchar(50)') AS ReportIdentifierNumber,
       RawXML.value('(/GrobXmlFile//Grob//ReportHeader//OrganizationNumber/node())[1]','int') AS OrginazationNumber
FROM Batches
person Morv    schedule 05.02.2013
comment
Это только первый узел для меня. - person Eric J. Price; 05.02.2013

arrow_upward
0
arrow_downward

если у вас есть только один xml в вашей таблице, вы можете преобразовать его за 2 шага:

CREATE TABLE Batches( 
   BatchID int,
   RawXml xml 
)

declare @xml xml=(select top 1 RawXml from @Batches)

SELECT  --b.BatchID,
        x.XmlCol.value('(ReportHeader/OrganizationReportReferenceIdentifier)[1]','VARCHAR(100)') AS OrganizationReportReferenceIdentifier,
        x.XmlCol.value('(ReportHeader/OrganizationNumber)[1]','VARCHAR(100)') AS OrganizationNumber
FROM    @xml.nodes('/CasinoDisbursementReportXmlFile/CasinoDisbursementReport') x(XmlCol)
person elle0087    schedule 26.11.2018