Post reply

More Advanced XML Processing Examples

  • May 15, 2007 at 10:33 am

    #172382

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2996.asp

    .

  • June 6, 2007 at 4:40 am

    #711053

    Cheers man, great article.

  • June 6, 2007 at 7:06 am

    #711090

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

     

  • June 6, 2007 at 7:30 am

    #711108

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

    Thanks a ton....

     

  • June 6, 2007 at 7:40 am

    #711112

    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 🙂

    .

  • June 13, 2007 at 1:22 am

    #712514

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

  • September 5, 2008 at 10:41 am

    #867908

    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:

    DECLARE @x XML

    SELECT @x = '

    Employee

    ContactInfo

    FirstName Bob /FirstName

    LastName /

    /ContactInfo

    /Employee'

    SELECT

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

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

    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?

    Thanks,

    Antonio

  • September 5, 2008 at 12:09 pm

    #867981

    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 🙂 ]

    CREATE XML SCHEMA COLLECTION NullTest AS '

    [xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"]

    [xsd:element name="Employee"]

    [xsd:complexType]

    [xsd:sequence]

    [xsd:element name="ContactInfo"]

    [xsd:complexType]

    [xsd:sequence]

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

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

    type="xsd:string"/]

    [/xsd:sequence]

    [/xsd:complexType]

    [/xsd:element]

    [/xsd:sequence]

    [/xsd:complexType]

    [/xsd:element]

    [/xsd:schema]'

    GO

    DECLARE @x XML(NullTest)

    SELECT @x = '

    [Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"]

    [ContactInfo]

    [FirstName]Bob[/FirstName]

    [LastName xsi:nil="true"/]

    [/ContactInfo]

    [/Employee]'

    SELECT

    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

    */

    .

  • September 5, 2008 at 1:17 pm

    #868010

    Very Nice, Thanks 😀

  • September 5, 2008 at 1:19 pm

    #868012

    Welcome 🙂

    .

  • September 18, 2008 at 5:35 am

    #873512

    I'm learning a lot from this. Thanks!

    Not to look a gift horse in the mouth (or publicly reveal my ignorance)...is 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?

  • September 25, 2008 at 4:32 am

    #876869

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

    .

  • September 25, 2008 at 4:49 am

    #876873

    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.

  • September 25, 2008 at 6:25 am

    #876920

    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.]

    .

  • September 25, 2008 at 6:53 am

    #876945

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

    http://www.sqlservercentral.com/articles/Miscellaneous/2996/

    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.

    Examples

    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