Блог

В настоящее время я создаю отчет с использованием хранимой процедуры. Хранимая процедура собирает информацию о необработанных файлах из таблицы файлов, которая похожа на следующую (упрощена для краткости):

Структура таблицы:

 //File Table Fields
FILE_ID (int)
FILE_DATE (string)
FILE_CONTENTS (XML)
FILE_CONTENTS_STRING (string)
PROCESSED (bool)
  

Хранимая процедура:

 //Grabs the ID and Date for each of the Unprocessed Files
SELECT [FILE_ID],
       [FILE_DATE]
  FROM [tbFILES]
 WHERE [PROCESSED] = 0
  

Я хотел бы вывести два дополнительных поля, которые находятся в FILE_CONTENTS (или FILE_CONTENTS_STRING ), которые оба находятся в XML / String в областях, подобных этим:

Часть структуры XML:

     <FID.4>
      <FID.4.1>TESTING</FID.4.1>     //File Header
    </FID.4>
    <FID.5>
      <FID.5.1>TEST</FID.5.1>        //Owner Last Name
      <FID.5.2>TEST</FID.5.2>        //Owner First Name
      <FID.5.3 />
      <FID.5.4 />
      <FID.5.5 />
      <FID.5.6 />
      <FID.5.7 />
      <FID.5.8 />
    </FID.5>
  

Чего я хотел бы достичь, так это вывести эти два значения (заголовок файла) и имя владельца (последнее, Первое) как часть вызова хранимой процедуры.

Вывод:

 [FILE_ID]      //From Table
[FILE_DATE]    //From Table
[FILE_HEADER]  //From FILE_CONTENTS in <FID.4.1></FID.4.1>
[FILE_OWNER]   //From FILE_CONTENTS in <FID.5.1></FID.5.1>,<FID.5.2></FID.5.2>
  

Возможно ли запросить этот тип информации из XML-файла (FILE_CONTENTS) или XML-файла в строковой форме (FILE_CONTENTS_STRING) с использованием хранимой процедуры SQL?

Редактировать: первая попытка (неудачная)

 SELECT FILE_ID,
       FILE_DATE,
       FILE_CONTENTS.value('(/PID.4/PID.4.1)[1]', 'varchar(16)') as FILE_HEADER,
       FILE_CONTENTS.value('(/PID.5/PID.5.1)[1]', 'varchar(16)')   ', '       
       FILE_CONTENTS.value('(/PID.5/PID.5.2)[1]', 'varchar(16)') as FILE_OWNER
  FROM [tbFILES]
  

который выдал значение NULL для полей FILE_HEADER и FILE_OWNER . Я полагаю, требуется что-то более сложное?

Повторное обновление: (Проблема решена!)

Мне пришлось добавить дополнительный раздел к XML, поскольку предоставленная часть изначально была фрагментом более крупного XML-документа.

Попробуйте этот запрос здесь — это а) работает и б) делает то, что вы ищете??

 -- test data setup
DECLARE @test TABLE (FILE_ID INT, FILE_DATE DATETIME, FILE_CONTENTS XML)

INSERT INTO @test VALUES(4711, '20110414', 
'<FID.4>
      <FID.4.1>TESTING</FID.4.1>     //File Header
    </FID.4>
    <FID.5>
      <FID.5.1>TEST_LN</FID.5.1>        //Owner Last Name
      <FID.5.2>TEST_FN</FID.5.2>        //Owner First Name
      <FID.5.3 />
      <FID.5.4 />
    </FID.5>')

-- select the values from the table and cross apply bits from the XML   
SELECT 
     FILE_ID ,
     FILE_DATE ,
     Node.value('(/FID.4/FID.4.1/text())[1]', 'varchar(50)') AS 'File_Header',
     Node.value('(/FID.5/FID.5.1/text())[1]', 'varchar(50)')   ', '  
     Node.value('(/FID.5/FID.5.2/text())[1]', 'varchar(50)') AS 'File_Owner'
FROM
    @test
CROSS APPLY
    FILE_CONTENTS.nodes('/*') AS Content(Node)
  

Мой вывод выглядит следующим образом:

 FILE_ID   FILE_DATE                File_Header   File_Owner
 4711     2011-04-14 00:00:00.000   TESTING      TEST_LN, TEST_FN
 4711     2011-04-14 00:00:00.000   TESTING      TEST_LN, TEST_FN
  

Если бы у вашего XML был правильный корневой элемент, вы, вероятно, могли бы сделать это еще лучше (с точки зрения производительности) — используйте правильный XPath в CROSS APPLY условии, чтобы захватить те биты повторяющихся узлов из XML, которые вы хотите.