时间戳

首页 > 解决方案 > Parse below XML in Sql Server

sql - Parse below XML in Sql Server

问题描述

The XML looks like this:

<saw:deliveryContent format="comma" disposition="attachment" xmlns:saw="uri">
    <saw:headline>
        <saw:caption>
            <saw:text>Beer PMIX Data</saw:text>
        </saw:caption>
    </saw:headline>
    <saw:attachmentMessage>
        <saw:caption>
            <saw:text>Beer PMIX data used in the PMIX Data - ROQ Stars file.</saw:text>
        </saw:caption>
    </saw:attachmentMessage>
    <saw:reportRef path="Beer PMIX (Scheduled)"/>
</saw:deliveryContent>

My result table should contain 

text ->Beer PMIX data used in the PMIX Data - ROQ Stars file.
reportRef path ->"Beer PMIX (Scheduled)

Like:text|reportRef path

Is it possible to get this values directly from a query or stored procedure?

标签： sqlsql-serverxml

解决方案

Your xml is invalid because there is no prefix declaration - saw.

Slightly modified xml can be parsed that way:

DECLARE @xml xml = '<saw:deliveryContent format="comma" disposition="attachment" xmlns:saw="uri">
    <saw:headline>
        <saw:caption>
            <saw:text>Beer PMIX Data</saw:text>
        </saw:caption>
    </saw:headline>
    <saw:attachmentMessage>
        <saw:caption>
            <saw:text>Beer PMIX data used in the PMIX Data - ROQ Stars file.</saw:text>
        </saw:caption>
    </saw:attachmentMessage>
    <saw:reportRef path="/shared/BJs Analytics/Departments/Planning/Jon/Agent Reports/Beer PMIX (Scheduled)"/>
</saw:deliveryContent>';

WITH XMLNAMESPACES ('uri' as saw)
SELECT
  @xml.value('(saw:deliveryContent/saw:headline/saw:caption/saw:text)[1]','nvarchar(max)'),
  @xml.value('(saw:deliveryContent/saw:attachmentMessage/saw:caption/saw:text)[1]','nvarchar(max)'),
  @xml.value('(saw:deliveryContent/saw:reportRef/@path)[1]','nvarchar(max)')

推荐阅读