i have a table that contains an XML column:
我有一个包含XML列的表:
CREATE TABLE Batches(
BatchID int,
RawXml xml
)
The xml contains items such as:
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
i've tried:
我试过了:
SELECT
foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
FROM CDRBatches.RawXML.query('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') foo
but that doesn't work. i've tried:
但这并不工作。我试过了:
SELECT
foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
FROM RawXML.nodes('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') bar(foo)
But that doesn't work. The XPath expression
但这并不工作。XPath表达式
/GrobReportXmlFileXmlFile/GrobReport/ReportHeader
is correct; in any other xml system it returns:
是正确的;在任何其他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>
So, it's obvious from my queries that i'd like to see. After reading a dozen Stackover questions and answers, i'm no closer to solving the problem.
从我的查询中可以明显看出我很想看到。读了一堆问题和答案之后,我离解决问题更近了一步。
3 个解决方案
#1
22
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);
Demo: SQLFiddle
演示:SQLFiddle
#2
2
This works, been tested...
其工作原理,经过测试……
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)
#3
1
Try this:
试试这个:
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
#1
22
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);
Demo: SQLFiddle
演示:SQLFiddle
#2
2
This works, been tested...
其工作原理,经过测试……
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)
#3
1
Try this:
试试这个:
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