Извлечение значений из поля XML в MS-SQL Server 2008

Я пытаюсь извлечь четыре части информации из базы данных MS-SQL Server 2008, которая хранится в одном поле XML. Это первый раз, когда мне приходилось работать с XML, поэтому у меня возникли некоторые проблемы, и поэтому у меня есть только те данные, которые я пытаюсь извлечь. Я пытался использовать другие публикации для решения своей проблемы, но, очевидно, безуспешно.

Четыре части информации — это сначала «Менеджер проекта», затем «Ценность», а затем 2-я — «Центр прибыли», а затем это значение. Значение из «Центра прибыли» будет использоваться для соединения двух таблиц. Ниже приведен пример данных XML, которые хранятся в этом поле.

    <ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <EntityPropertyOfString>
       <Name>Project Manager</Name>
       <Value>DBD</Value>
     </EntityPropertyOfString>
     <EntityPropertyOfString>
       <Name>Profit Center</Name>
       <Value>211</Value>
     </EntityPropertyOfString>
    </ArrayOfEntityPropertyOfString>

Итак, в этом примере мне нужно использовать значение «211» «Центр прибыли», чтобы объединить две таблицы в запросе MS-SQL. Таблицу, в которой содержится эта информация, можно назвать "tblProfitCenter", а поле, содержащее ее, - "prftData".

Вот составленный запрос, который выполнял бы ту же работу, если бы данные в «prftData» были не в формате XML, а вместо этого были бы обычным целочисленным полем, содержащим идентификатор центра прибыли и выполняющим соединение.

    SELECT md.LName, md.FName, pc.ProfitCenterName
    FROM tblMainDataCenter md
    LEFT OUTER JOIN tblProfitCenter pc ON md.pcID = pc.prftData

Это для проекта, в котором я работаю, и мне нужно иметь возможность выйти за рамки этого. Обычно я изучаю XML, чтобы решить эту проблему, но время не позволяет этого. Пока у меня не будет возможности изучить XML, я буду признателен за любую помощь.


xml sql xml-parsing
person Nosharu    schedule 26.07.2013    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Вы можете использовать функцию value для столбца XML для извлечения данных из XML следующим образом:

SELECT col.value('(/ArrayOfEntityPropertyOfString/EntityPropertyOfString[Name="Profit Center"]/Value)[1]', 'int')
FROM tbl

предполагая, что ваша таблица называется tbl, а столбец XML называется col.

Первый аргумент — это выражение XPath 1, а второй — целевой тип данных. Обратите внимание, что эти строки должны быть строками исправления в SQL Server и не могут создаваться динамически, например. грамм. путем конкатенации строк.

person FrankPl    schedule 26.07.2013

arrow_upward
3
arrow_downward

Следующий запрос позволит вам взять содержимое xml и поместить его в формат таблицы, чтобы вы могли затем выполнять необходимые операции sql с этой таблицей:

Declare @xmlstring xml = '<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <EntityPropertyOfString>
       <Name>Project Manager</Name>
       <Value>DBD</Value>
     </EntityPropertyOfString>
     <EntityPropertyOfString>
       <Name>Profit Center</Name>
       <Value>211</Value>
     </EntityPropertyOfString>
    </ArrayOfEntityPropertyOfString>'


select MainDataCenter.Col.value('(Name)[1]','varchar(max)') as Name
,MainDataCenter.Col.value('(Value)[1]','varchar(max)') as Value
from @xmlstring.nodes('/ArrayOfEntityPropertyOfString/EntityPropertyOfString') as MainDataCenter(Col)
person Petio Ivanov    schedule 26.07.2013