При запросе XML в SQL Server 2005 несколько тегов документа находятся в одной строке

Обновление для уточнения

Вот полная копия одного из 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>

Хорошо, так что для уточнения вот мой полный запрос.

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

И это результат, который я получаю.

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

Это проблема, так как мне нужно видеть данные из обеих копий узла в отдельных строках.

Поэтому у меня есть много документов 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>

Что я импортировал в поле 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

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

т.е.

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

когда это должно быть

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

Если бы у меня было [1] до конца XML-запроса, как это

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

он будет сбрасывать второй идентификатор раздела все вместе, который не работает, потому что мне нужны оба.

Любая помощь?

Источник

2 ответа

Решение

Если вы хотите выделить несколько "строк" ​​из 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)

Это заявление XPath в FROM Предложение в основном определяет "псевдотаблица" элементов XML - на основе этого XPath. Итак, здесь вы получите псевдотаблица для каждого <Section> запись в вашем XML - из которого вы можете выбрать отдельные элементы, используя .value() функция.

Если вы хотите выбрать это из таблицы, которая содержит столбец типа 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)
Источник

Это будет работать..., например:

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

Если есть 3 элемента OriginatorAgency.

Источник
Другие вопросы по тегам