CODEWITHSUNDEEP
sql-serverxmlsql-server-2008t-sql
iCoder
iCoder

Reputation: 39

How to parse xml data with ignore some tags

Have xml like :

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>'

We must ignore first data which is after a1 and parse data which is between c1 . We can have many tags with c1 and need to parse all. For example I write xml with two c1 tags,response must looks like : enter image description here How to do it ?

Upvotes: 2

Views: 239

Answers (2)

Shiju Shaji
Shiju Shaji

Reputation: 1730

Please see the below query. Following is the T-SQL script which we will be used to read the XML:

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)

Upvotes: 0

Eralper
Eralper

Reputation: 6622

Please check following tutorial for a similar case explaining how SQL programmers can use SQL XML queries

For your case you can use below SQL XML query, I only added 4 columns for simplicity

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)

I hope it helps for solution

Upvotes: 1

Related Questions