Блог

Главная - Вопросы по программированию - Как проанализировать XML, который хранится в столбце в SQL Server

Как проанализировать XML, который хранится в столбце в SQL Server

#sql-server #xml #parsing

#sql-сервер #xml #синтаксический анализ

Вопрос:

Я хочу проанализировать XML, который хранится в столбце в SQL Server:

Столбец таблицы с XML на этом рисунке

и XML является:

  <?xml version="1.0" encoding="UTF-8"?>
    <propertylist id="root">
       <property expanded="Y" id="createsdi" selectedindex="0" type="collection">
          <collection>
             <propertylist id="1305619640064">
                <property expanded="Y" id="columnvalues" selectedindex="0" type="collection">
                   <collection>
                      <propertylist id="1396440519721" />
                   </collection>
                </property>
                <property expanded="Y" id="tests" selectedindex="0" type="collection">
                   <collection>
                      <propertylist id="p1602059752707" sequence="1000000">
                         <property id="id" type="simple"><![CDATA[item1]]></property>
                         <property id="workitemid" type="simple"><![CDATA[SOURAVTESTER|1]]></property>
                      </propertylist>
                   </collection>
                </property>
                <property expanded="Y" id="specs" selectedindex="0" type="collection">
                   <collection>
                      <propertylist id="p1602059825237" sequence="1000000">
   

                  <property id="id" type="simple"><![CDATA[item1]]></property>
                     <property id="specid" type="simple"><![CDATA[EMSpec|1]]></property>
                  </propertylist>
               </collection>
            </property>
         </propertylist>
      </collection>
   </property>
   <property expanded="Y" id="workorder" type="propertylist">
      <propertylist id="root_0">
         <property expanded="Y" id="graceperiod" type="propertylist">
            <propertylist id="root_0_workorder_0">
               <property expanded="Y" id="graceperiod" type="propertylist">
                  <propertylist id="root_0_workorder_0_graceperiod_0" />
               </property>
               <property expanded="Y" id="deviation" type="propertylist">
                  <propertylist id="root_0_workorder_0_graceperiod_0" />
               </property>
            </propertylist>
         </property>
      </propertylist>
   </property>
</propertylist>

Как я могу проанализировать значения workitemid, specid?

Я написал следующий код

 SELECT
    ID,
    ParentPropertyId = xc.value('(../@id)[1]', 'varchar(100)'),
    WorkItemId = xc.value('(.)[1]', 'varchar(100)')
FROM
    scheduleplanitem
CROSS APPLY
    valuetree.nodes('//propertylist/property[@id="workitemid"]') xt(xc)

но я получаю сообщение об ошибке

Метод XMLDT ‘nodes’ может быть вызван только для столбцов типа xml

Результат должен быть:

 workitemid  SOURAVTESTER
specid      EMSpec

Комментарии:

1. Пожалуйста, отредактируйте свой пост и укажите желаемый результат.

Ответ №1:

Пожалуйста, попробуйте следующее решение.

Он выполняет следующее:

  • Удаляет XML prolog с кодировкой UTF-8. SQL Server неявно преобразует любой XML внутренне в UTF-16.
  • Преобразует тип данных NVARCHAR() в тип данных XML. После этого методы XQuery для обработки XML становятся доступными.

SQL

 -- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col NVARCHAR(MAX));
INSERT INTO @tbl (col) VALUES
(N'<?xml version="1.0" encoding="UTF-8"?>
<propertylist id="root">
    <property expanded="Y" id="createsdi" selectedindex="0" type="collection">
        <collection>
            <propertylist id="1305619640064">
                <property expanded="Y" id="columnvalues" selectedindex="0"
                          type="collection">
                    <collection>
                        <propertylist id="1396440519721"/>
                    </collection>
                </property>
                <property expanded="Y" id="tests" selectedindex="0"
                          type="collection">
                    <collection>
                        <propertylist id="p1602059752707" sequence="1000000">
                            <property id="id" type="simple"><![CDATA[item1]]></property>
                            <property id="workitemid" type="simple"><![CDATA[SOURAVTESTER|1]]></property>
                        </propertylist>
                    </collection>
                </property>
                <property expanded="Y" id="specs" selectedindex="0"
                          type="collection">
                    <collection>
                        <propertylist id="p1602059825237" sequence="1000000">


                            <property id="id" type="simple"><![CDATA[item1]]></property>
                            <property id="specid" type="simple"><![CDATA[EMSpec|1]]></property>
                        </propertylist>
                    </collection>
                </property>
            </propertylist>
        </collection>
    </property>
    <property expanded="Y" id="workorder" type="propertylist">
        <propertylist id="root_0">
            <property expanded="Y" id="graceperiod" type="propertylist">
                <propertylist id="root_0_workorder_0">
                    <property expanded="Y" id="graceperiod" type="propertylist">
                        <propertylist id="root_0_workorder_0_graceperiod_0"/>
                    </property>
                    <property expanded="Y" id="deviation" type="propertylist">
                        <propertylist id="root_0_workorder_0_graceperiod_0"/>
                    </property>
                </propertylist>
            </property>
        </propertylist>
    </property>
</propertylist>')
, (NULL);
-- DDL and sample data population, end

--DECLARE @startPos INT = LEN('<?xml version="1.0" encoding="utf-8"?>')   1;
DECLARE @prolog NVARCHAR(100) = '<?xml version="1.0" encoding="utf-8"?>';

;WITH rs AS
(
    SELECT ID
        --, TRY_CAST(SUBSTRING(col
        --  , @startPos
        --  , LEN(col)) AS XML) AS xmldata
        , TRY_CAST('<root>'   REPLACE(col, @prolog, '')   '</root>' AS XML) AS xmldata  FROM @tbl
)
SELECT ID
    , c.value('(./text())[1]', 'VARCHAR(100)') AS Item
FROM rs
    CROSS APPLY xmldata.nodes('//property[@id=("specid","workitemid")]') AS t(c);

Вывод

  ---- ---------------- 
| ID |      Item      |
 ---- ---------------- 
|  1 | SOURAVTESTER|1 |
|  1 | EMSpec|1       |
 ---- ----------------

Комментарии:

1. Существует столбец со всем XML. Я должен проверить весь XML и найти значения. Пожалуйста, проверьте изображение, прикрепленное к описанию.

2. @sourav, вы несете ответственность за предоставление DDL и выборки данных. Не изображения i t.

3. @sourav, я скорректировал ответ.

4. «Столбец таблицы с XML на этом рисунке» — пожалуйста, проверьте ссылку выше, сэр.

5. @sourav, я скорректировал ответ. Проверьте это.