在SQL Server 2005中查询XML时，文档的多个标记位于同一行

Update to provide clarification

更新以提供说明

Here's a full copy of one of the xml files I"m having trouble with.

这是我遇到问题的一个xml文件的完整副本。

<Grower_Run xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Puller xsi:type="Puller">
    <Puller_Number xsi:type="xsd:int">16</Puller_Number>
  </Puller>
  <Run_ID xsi:type="xsd:string">SA1611030B</Run_ID>
  <Crucible xsi:type="Crucible">
    <Crucible_Type xsi:type="xsd:string">RWNTYPE</Crucible_Type>
    <Section>
      <Grower_Run_Section>
        <SectionID xsi:type="xsd:string">SA1611030B1</SectionID>
        <Crystal_Growth>
          <Growth_StartTime xsi:type="xsd:dateTime">2011-03-01T12:59:30</Growth_StartTime>
          <Growth_Process>
            <Growth_Process>
              <Process_Name xsi:type="xsd:string">BODY</Process_Name>
              <Single_Moment_Snapshot>
                <Single_Moment_Snapshot>
                  <Snapshot_Name xsi:type="xsd:string">Body Start</Snapshot_Name>
                  <Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-01T17:11:30</Snapshot_Datetime>
                  <Ingot_Length xsi:type="xsd:decimal">228.19</Ingot_Length>
                  <Heater_Temp xsi:type="xsd:decimal">1337.09</Heater_Temp>
                  <LS_Temp xsi:type="xsd:decimal">1243.00</LS_Temp>
                  <Heater_Power xsi:type="xsd:decimal">56.10</Heater_Power>
                </Single_Moment_Snapshot>
                <Single_Moment_Snapshot>
                  <Snapshot_Name xsi:type="xsd:string">Mid Body</Snapshot_Name>
                  <Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-01T17:11:30</Snapshot_Datetime>
                  <Ingot_Length xsi:type="xsd:decimal">228.19</Ingot_Length>
                  <Heater_Temp xsi:type="xsd:decimal">1337.09</Heater_Temp>
                  <LS_Temp xsi:type="xsd:decimal">1243.00</LS_Temp>
                  <Heater_Power xsi:type="xsd:decimal">56.10</Heater_Power>
                </Single_Moment_Snapshot>
                <Single_Moment_Snapshot>
                  <Snapshot_Name xsi:type="xsd:string">Tail Start</Snapshot_Name>
                  <Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-02T01:34:24</Snapshot_Datetime>
                  <Ingot_Length xsi:type="xsd:decimal">230.40</Ingot_Length>
                  <Heater_Temp xsi:type="xsd:decimal">1338.20</Heater_Temp>
                  <LS_Temp xsi:type="xsd:decimal">1243.40</LS_Temp>
                  <Heater_Power xsi:type="xsd:decimal">56.39</Heater_Power>
                </Single_Moment_Snapshot>
              </Single_Moment_Snapshot>
            </Growth_Process>
          </Growth_Process>
          <Growth_FinishTime xsi:type="xsd:dateTime">2011-03-02T01:35:24</Growth_FinishTime>
        </Crystal_Growth>
      </Grower_Run_Section>
      <Grower_Run_Section>
        <SectionID xsi:type="xsd:string">SA1611030B9</SectionID>
        <Crystal_Growth>
          <Growth_StartTime xsi:type="xsd:dateTime">2011-03-02T04:02:37</Growth_StartTime>
          <Growth_Process>
            <Growth_Process>
              <Process_Name xsi:type="xsd:string">BODY</Process_Name>
              <Single_Moment_Snapshot>
                <Single_Moment_Snapshot>
                  <Snapshot_Name xsi:type="xsd:string">Body Start</Snapshot_Name>
                  <Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-02T07:54:39</Snapshot_Datetime>
                  <Ingot_Length xsi:type="xsd:decimal">231.80</Ingot_Length>
                  <Heater_Temp xsi:type="xsd:decimal">1340.00</Heater_Temp>
                  <LS_Temp xsi:type="xsd:decimal">1246.70</LS_Temp>
                  <Heater_Power xsi:type="xsd:decimal">56.60</Heater_Power>
                </Single_Moment_Snapshot>
                <Single_Moment_Snapshot>
                  <Snapshot_Name xsi:type="xsd:string">Mid Body</Snapshot_Name>
                  <Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-02T07:54:39</Snapshot_Datetime>
                  <Ingot_Length xsi:type="xsd:decimal">231.80</Ingot_Length>
                  <Heater_Temp xsi:type="xsd:decimal">1340.00</Heater_Temp>
                  <LS_Temp xsi:type="xsd:decimal">1246.70</LS_Temp>
                  <Heater_Power xsi:type="xsd:decimal">56.60</Heater_Power>
                </Single_Moment_Snapshot>
                <Single_Moment_Snapshot>
                  <Snapshot_Name xsi:type="xsd:string">Tail Start</Snapshot_Name>
                  <Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-03T06:47:19</Snapshot_Datetime>
                  <Ingot_Length xsi:type="xsd:decimal">1778.00</Ingot_Length>
                  <Heater_Temp xsi:type="xsd:decimal">1388.80</Heater_Temp>
                  <LS_Temp xsi:type="xsd:decimal">1330.70</LS_Temp>
                  <Heater_Power xsi:type="xsd:decimal">63.50</Heater_Power>
                </Single_Moment_Snapshot>
              </Single_Moment_Snapshot>
            </Growth_Process>
          </Growth_Process>
          <Growth_FinishTime xsi:type="xsd:dateTime">2011-03-03T06:48:19</Growth_FinishTime>
        </Crystal_Growth>
      </Grower_Run_Section>
    </Section>
  </Crucible>
</Grower_Run>

Ok so for clarification here is my full query.

好的，为了澄清这里是我的完整查询。

DECLARE @FILES TABLE(FILENAME VARCHAR(20),DEPTH INT,FILE_FLAG INT)
DECLARE @XML_TABLE TABLE(
    xmlFileName VARCHAR(300),
    xml_data xml
)
DECLARE @xmlFileName VARCHAR(300)
DECLARE @FILENAME VARCHAR(20)

INSERT INTO @FILES
EXEC Master.dbo.xp_DirTree '\\SASSOAPPSRV\Grower XML Files\',1,1

DECLARE XML_CURSOR CURSOR FOR SELECT FILENAME FROM @FILES

OPEN XML_CURSOR
FETCH NEXT FROM XML_CURSOR 
INTO @FILENAME

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT  @xmlFileName = '\\SASSOAPPSRV\Grower XML Files\' + @FILENAME

INSERT INTO @XML_TABLE(xmlFileName, xml_data)
EXEC('
SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
    SELECT  * 
    FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
FETCH NEXT FROM XML_CURSOR 
INTO @FILENAME

END
CLOSE XML_CURSOR
DEALLOCATE XML_CURSOR

DECLARE @PARSED_XML TABLE(
S VARCHAR(200),
RUN_ID VARCHAR(20)
,SECTION_ID VARCHAR(50)
)



INSERT INTO @PARSED_XML
SELECT 
T.xmlFileName AS S,
    t.xml_data.value('(/Grower_Run/Run_ID)[1]', 'varchar(50)') AS 'RunID',
    Section.value('(Grower_Run_Section/SectionID)[1]', 'varchar(50)') as 'SectionID'
FROM
    @xml_table t
CROSS APPLY
    t.xml_data.nodes('/Grower_Run/Crucible/Section') AS Tmp(Section)

SELECT 
S
,RUN_ID
,SECTION_ID
 FROM @PARSED_XML
WHERE RUN_ID = 'SA1611030B'
 ORDER BY RUN_ID

And this is the result I'm getting.

这就是我得到的结果。

\\SASSOAPPSRV\Grower XML Files\SA1611030B.xml   SA1611030B  SA1611030B1

This is the issue since I need to see data from both copies of the node in separate rows.

这是问题，因为我需要在单独的行中查看来自节点的两个副本的数据。

So I have many XML documents with the following structure

所以我有许多具有以下结构的XML文档

<Grower_Run xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Run_ID xsi:type="xsd:string">SA0111023B</Run_ID>
  <Crucible xsi:type="Crucible">
    <Section>
      <Grower_Run_Section>
        <SectionID xsi:type="xsd:string">SA0111023B1</SectionID>
      </Grower_Run_Section>
    </Section>
    <Section>
      <Grower_Run_Section>
        <SectionID xsi:type="xsd:string">SA0111023B9</SectionID>
      </Grower_Run_Section>
    </Section>
  </Crucible>
</Grower_Run>

That I've imported into an XML field of a temp table which I'm querying against with a query like this one

我已经导入临时表的XML字段，我正在使用这样的查询来查询

SELECT
   CAST(XML_DATA.query('data(/Grower_Run/Run_ID)') AS VARCHAR(20)) AS [RUN ID]
  ,CAST(XML_DATA.query('data(/Grower_Run/Crucible/Section/Grower_Run_Section/SectionID)') AS VARCHAR(50)) AS [SECTION ID]
FROM @XML_TABLE

So the problem is that my results from the multiple instances of the "SectionID" tag are showing up in the same row.

所以问题是来自“SectionID”标签的多个实例的结果显示在同一行中。

i.e.

即

   RunID    |   SectionID
--------------------------
SA0111023B  |    SA0111023B1  SA0111023B9

when it should be

什么时候应该

RunID       |   SectionID
-----------------------------
SA0111023B  |    SA0111023B1  
SA0111023B  |    SA0111023B9

If I had [1] to then end of the xml query like this

如果我有[1]然后结束这样的xml查询

CAST(XML_DATA.query('data(/Grower_Run/Crucible/Section/Grower_Run_Section/SectionID)')[1]

it will drop the second section ID all together which doesn't work cuz I need both.

它会将第二部分ID全部放在一起但是不起作用因为我需要两者。

Any help?

有帮助吗？

2 个解决方案

#1

If you want to select out multiple "rows" from a XML document, you need to use the .nodes() SQL Server XML function - something like this:

如果要从XML文档中选择多个“行”，则需要使用.nodes（）SQL Server XML函数 - 如下所示：

SELECT 
    @XMLTable.value('(/Grower_Run/Run_ID)[1]', 'varchar(50)') AS 'RunID',
    Section.value('(Grower_Run_Section/SectionID)[1]', 'varchar(50)') as 'SectionID'
FROM
    @XMLTable.nodes('/Grower_Run/Crucible/Section') AS Tmp(Section)

That XPath statement in the FROM clause basically defines a "pseudo-table" of XML elements - based on that XPath. So here you get a pseudo-table for each <Section> entry in your XML - from which you can then select individual elements using the .value() function.

FROM子句中的XPath语句基本上定义了XML元素的“伪表” - 基于该XPath。因此，您可以在XML中为每个

条目获取一个伪表 - 然后您可以使用.value（）函数从中选择单个元素。

If you want to select that from a table that contains a column of type XML, you might need to check into the CROSS APPLY command:

如果要从包含XML类型列的表中选择，则可能需要检查CROSS APPLY命令：

SELECT 
    t.SomeColumn,        
    t.XmlColumn.value('(/Grower_Run/Run_ID)[1]', 'varchar(50)') AS 'RunID',
    Section.value('(Grower_Run_Section/SectionID)[1]', 'varchar(50)') as 'SectionID'
FROM
    dbo.YourTable t
CROSS APPLY
    t.XmlColumn.nodes('/Grower_Run/Crucible/Section') AS Tmp(Section)

#2

this will work..., for example:

这将工作......，例如：

pref.value('(OriginatorAgency/text())[1]','varchar(50)'),
pref.value('(OriginatorAgency/text())[2]','varchar(50)'),
pref.value('(OriginatorAgency/text())[3]','varchar(50)')

If there are 3 OriginatorAgency items.

如果有3个OriginatorAgency项目。

#1