CODEWITHSUNDEEP
sqlxmlt-sql
John Waclawski
John Waclawski

Reputation: 996

Parsing an unknown amount of Elements in XML with T-SQL

I'm pulling a XML field from a table. I'm parsing the data out of it & initially I have no issues pulling standard element info. My question is this. What if I encounter a XML string & it has an unknown amount of "Notes" elements for example? It may have 1, 2, 3, 10, 30 or whatever amount of "Notes" elements. Below is an example:

<Person>
    <PersonID>
     123456 
        <PersonName>
        John Doe 
            <Note>
                <PersonID>9876</PersonID>
                <NoteType>C</NoteType>
                <NoteCount>1</NoteCount>
                <Note>AX</Note>
            </Note>
            <Note>
                <PersonID>534354345</PersonID>
                <NoteType>C</NoteType>
                <NoteCount>2</NoteCount>
                <NoteDate>1994-05-17T00:00:00-04:00</NoteDate>
                <Note>Note #2</Note>
            </Note>
            <Note>
                <PersonID>649349873498</PersonID>
                <NoteType>C</NoteType>
                <NoteCount>3</NoteCount>
                <NoteDate>1994-06-24T00:00:00-04:00</NoteDate>
                <Note>More notes are in here.</Note>
            </Note>
            <Note>
                <PersonID>432834987430987AAAAAA</PersonID>
                <NoteType>C</NoteType>
                <NoteCount>4</NoteCount>
                <NoteDate>1994-06-29T00:00:00-04:00</NoteDate>
                <Note>And this is the last note element. </Note>
            </Note>
        </PersonName>
    </PersonID>
</Person>

Is there a way in t-sql to parse out an unknown amount of elements? If so, how?

Thanks!!

Upvotes: 1

Views: 113

Answers (1)

Cameron Tinker
Cameron Tinker

Reputation: 9789

It doesn't make much sense to nest the <PersonName> element in the <PersonId> element as you currently have it. For the sake of parsing your XML, it would be easier to have individual elements contain text or child elements and not both. I would recommend something like this:

<Person>
    <PersonID>123456</PersonID>
    <PersonName>John Doe</PersonName>
    <Note>
        <PersonID>9876</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>1</NoteCount>
        <NoteMessage>AX</NoteMessage>
    </Note>
    <Note>
        <PersonID>534354345</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>2</NoteCount>
        <NoteDate>1994-05-17T00:00:00-04:00</NoteDate>
        <NoteMessage>Note #2</NoteMessage>
    </Note>
    <Note>
        <PersonID>649349873498</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>3</NoteCount>
        <NoteDate>1994-06-24T00:00:00-04:00</NoteDate>
        <NoteMessage>More notes are in here.</NoteMessage>
    </Note>
    <Note>
        <PersonID>432834987430987AAAAAA</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>4</NoteCount>
        <NoteDate>1994-06-29T00:00:00-04:00</NoteDate>
        <NoteMessage>And this is the last note element.</NoteMessage>
    </Note> 
</Person>

To reduce ambiguity issues, I renamed the inner <Note> element to <NoteMessage>. Once your XML is in a form similar to this, you can use XPATH to traverse your XML: 

DECLARE @xml xml
SET @xml = 
'<Person>
    <PersonID>123456</PersonID>
    <PersonName>John Doe</PersonName>
    <Note>
        <PersonID>9876</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>1</NoteCount>
        <NoteMessage>AX</NoteMessage>
    </Note>
    <Note>
        <PersonID>534354345</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>2</NoteCount>
        <NoteDate>1994-05-17T00:00:00-04:00</NoteDate>
        <NoteMessage>Note #2</NoteMessage>
    </Note>
    <Note>
        <PersonID>649349873498</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>3</NoteCount>
        <NoteDate>1994-06-24T00:00:00-04:00</NoteDate>
        <NoteMessage>More notes are in here.</NoteMessage>
    </Note>
    <Note>
        <PersonID>432834987430987AAAAAA</PersonID>
        <NoteType>C</NoteType>
        <NoteCount>4</NoteCount>
        <NoteDate>1994-06-29T00:00:00-04:00</NoteDate>
        <NoteMessage>And this is the last note element.</NoteMessage>
    </Note> 
</Person>'

SELECT
    n.value('(./PersonID/text())[1]', 'Varchar(50)') as PersonID,
    n.value('(./NoteType/text())[1]', 'char(1)') as NoteType,
    n.value('(./NoteCount/text())[1]', 'int') as NoteCount,
    n.value('(./NoteDate/text())[1]', 'datetime') as NoteDate,
    n.value('(./NoteMessage/text())[1]', 'Varchar(50)') as NoteMessage
FROM @xml.nodes('/Person/Note') as a(n)

Upvotes: 1

Related Questions