如何使用忽略某些标记来解析xml数据

Question

拥有以下xml：

declare @xml XML = '<a1>
    <d2>0146</d2>
    <d3>32926318</d3>
    <d4>04/08/2015</d4>
    <d5>04/08/2015</d5>
    <d6>0.00</d6>
    <b1>
        <c1>
            <s1>69541</s1>
            <s2>04/08/2015</s2>
            <s3>B</s3>
            <s4>GH</s4>
            <s5>20.00</s5>
            <s6>32926320 abc abc</s6>
            <s7></s7>
            <s8></s8>
            <s9></s9>
            <s10></s10>
            <s11>12:01:28</s11>
            <s12>0.00</s12>
            <s13>ABC ABC ABC</s13>
            <s14>624</s14>
            <s15>4620392741</s15>
            <s16>4620392741</s16>
            <s17>ABC123</s17>
        </c1>
        <c1>
            <s1>69541</s1>
            <s2>04/08/2015</s2>
            <s3>B</s3>
            <s4>GH</s4>
            <s5>20.00</s5>
            <s6>32926320 abc abc</s6>
            <s7></s7>
            <s8></s8>
            <s9></s9>
            <s10></s10>
            <s11>12:01:28</s11>
            <s12>0.00</s12>
            <s13>ABC ABC ABC</s13>
            <s14>624</s14>
            <s15>4620392741</s15>
            <s16>4620392741</s16>
            <s17>ABC123</s17>
        </c1>
    </b1>
</a1>'

我们必须忽略a1之后的第一个数据和c1之间的数据解析。我们可以使用c1拥有许多标记，并且需要解析所有的标记。例如，我用两个c1标记编写xml，响应必须如下所示：

怎么做？

Answer 1

请查看下面的教程，以了解SQL程序员如何使用SQL XML查询的类似案例。

对于您的情况，您可以使用下面的SQL XML查询，为了简单起见，我只添加了4列。

SELECT
 xmlrow.value('s1[1]','varchar(100)') as [s1],
 xmlrow.value('s2[1]','varchar(100)') as [s2],
 xmlrow.value('s3[1]','varchar(100)') as [s3],
 xmlrow.value('s4[1]','varchar(100)') as [s4]
FROM @xml.nodes('/a1/b1/c1') as xmltbl(xmlrow) 

我希望它能帮助解决问题。

Answer 2

请参阅以下查询。下面是用于读取XML的to脚本：

SELECT a.b.value('c1[1]/s1[1]', 'varchar(10)') AS s1
    ,a.b.value('c1[1]/s2[1]', 'varchar(10)') AS s2
    ,a.b.value('c1[1]/s3[1]', 'varchar(10)') AS s3
    ,a.b.value('c1[1]/s4[1]', 'varchar(10)') AS s4
    ,a.b.value('c1[1]/s5[1]', 'varchar(10)') AS s5
    ,a.b.value('c1[1]/s6[1]', 'varchar(10)') AS s6
    ,a.b.value('c1[1]/s7[1]', 'varchar(10)') AS s7
    ,a.b.value('c1[1]/s8[1]', 'varchar(10)') AS s8
    ,a.b.value('c1[1]/s9[1]', 'varchar(10)') AS s9
    ,a.b.value('c1[1]/s10[1]', 'varchar(10)') AS s10
    ,a.b.value('c1[1]/s11[1]', 'varchar(10)') AS s11
    ,a.b.value('c1[1]/s12[1]', 'varchar(10)') AS s12
    ,a.b.value('c1[1]/s13[1]', 'varchar(10)') AS s13
    ,a.b.value('c1[1]/s14[1]', 'varchar(10)') AS s14
    ,a.b.value('c1[1]/s15[1]', 'varchar(10)') AS s15
    ,a.b.value('c1[1]/s16[1]', 'varchar(10)') AS s16
    ,a.b.value('c1[1]/s17[1]', 'varchar(10)') AS s17
FROM @xml.nodes('a1/b1') a(b)

UNION ALL

SELECT a.b.value('c1[2]/s1[1]', 'varchar(10)') AS s1
    ,a.b.value('c1[2]/s2[1]', 'varchar(10)') AS s2
    ,a.b.value('c1[2]/s3[1]', 'varchar(10)') AS s3
    ,a.b.value('c1[2]/s4[1]', 'varchar(10)') AS s4
    ,a.b.value('c1[2]/s5[1]', 'varchar(10)') AS s5
    ,a.b.value('c1[2]/s6[1]', 'varchar(10)') AS s6
    ,a.b.value('c1[2]/s7[1]', 'varchar(10)') AS s7
    ,a.b.value('c1[2]/s8[1]', 'varchar(10)') AS s8
    ,a.b.value('c1[2]/s9[1]', 'varchar(10)') AS s9
    ,a.b.value('c1[2]/s10[1]', 'varchar(10)') AS s10
    ,a.b.value('c1[2]/s11[1]', 'varchar(10)') AS s11
    ,a.b.value('c1[2]/s12[1]', 'varchar(10)') AS s12
    ,a.b.value('c1[2]/s13[1]', 'varchar(10)') AS s13
    ,a.b.value('c1[2]/s14[1]', 'varchar(10)') AS s14
    ,a.b.value('c1[2]/s15[1]', 'varchar(10)') AS s15
    ,a.b.value('c1[2]/s16[1]', 'varchar(10)') AS s16
    ,a.b.value('c1[2]/s17[1]', 'varchar(10)') AS s17
FROM @xml.nodes('a1/b1') a(b)