More Advanced XML Processing Examples

  • Comments posted here are about the content posted at


  • Cheers man, great article.

  • Excellent Article, would love to see a cursor/loop example too.


  • Awesome...!!!I had been looking for this kind of example at one place from a long time..u heard me..!!!

    Thanks a ton....


  • I am glad to know that the information was helpful. There are a few more posts scheduled and will appear in the coming weeks. So keep a watch 🙂


  • thanks - given me lots of ideas where this could be useful.

  • Great Articles, and we have incorporated from your articles into our projects!!!

    I have a question on when our .net apps creates the xml to pass into our stored procedure, sometimes an element may not have a value which is fine, and we want to shred this xml into a relational table, but the empty element seems to be handled as an empty string versus a null. We want it to be null.

    How we do it today:


    SELECT @x = '



    FirstName Bob /FirstName

    LastName /






    FROM @x.nodes('/Employee/ContactInfo') n(x)

    Then we would use something like this from a our temp table to clean up data if it is empty string and replace with Null value.

    IF @lastName = '' SET @lastName = NULL

    So if you our xml has many parameters, our stored proc gets peppered with all these statements, so I am just looking for a better or clean way to do this?



  • Antonio,

    You might need to work with TYPED XML to achieve this. You need to create a schema collection and set the element to be "nillable". Then in your XML instance, you need to quality the element with "xsi:nil" attribute to indicate that the value of element is NULL.

    Here is an example [replace square brackets with xml tags before you run it 🙂 ]


    [xsd:schema xmlns:xsd=""]

    [xsd:element name="Employee"]



    [xsd:element name="ContactInfo"]



    [xsd:element name="FirstName" type="xsd:string"/]

    [xsd:element name="LastName" nillable="true"










    DECLARE @x XML(NullTest)

    SELECT @x = '

    [Employee xmlns:xsi=""]



    [LastName xsi:nil="true"/]




    x.value('(FirstName)[1]','varchar(30)') AS FirstName,

    x.value('(LastName)[1]','varchar(30)') AS LastName

    FROM @x.nodes('/Employee/ContactInfo') n(x)


    FirstName LastName

    ------------------------------ ------------------------------

    Bob NULL



  • Very Nice, Thanks 😀

  • Welcome 🙂


  • I'm learning a lot from this. Thanks!

    Not to look a gift horse in the mouth (or publicly reveal my ignorance) the raw source code available somewhere? Having to delete the line numbers, and remove the double spacing that happens when you copy from HTML, makes testing your code a bit of a pain. Or does everyone else know the secret?

  • The title of each example that says "example #" is a link. You can click on that to download the source code of each example.


  • Thanks Jacob. That would make life easier, but when I try it I just get:

    Articles with tags Miscellaneous, 2996, axp1, axp2.txt

    Search for any content tagged Miscellaneous & 2996 & axp1 & axp2.txt

    Sorry, nothing found for this search

    Also tried it from a later article where you had "Step #" as links, but got the same "nothing found" result.

  • May be there is a misunderstanding. On the top of each example, (on the left side) there is a hyper link that helps you to download a text file containing the source code. Are you able to locate that? [The title of the hyper link is like Example 1, Example 2 etc.]


  • Now I really feel dumb. I open the page at:

    and see:


    One of the most useful methods exposed by the XML data type is the Value() method. Here, I am presenting 9 more examples which demonstrates the different XML operations that we could perform with the Value() method.


    Example 1

    1 /*

    2 The following TSQL retrieves attribute values from the XML variable.

    3 Attribute names are prefixed with "@".


    The line "Example 1" is a link, but when I click it, I still get a new page saying:

    Articles with tags Miscellaneous, 2996, axp1, axp1.txt

    Search for any content tagged Miscellaneous & 2996 & axp1 & axp1.txt

    Sorry, nothing found for this search

    Am I still looking in the wrong place, or should we just blame Steve Jones :-}

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply