Post reply

Child Node

  • June 16, 2008 at 4:38 pm

    #75239

    DECLARE @UserXML XML

    SET @UserXML = '

    '

    SELECT

    tblXML.Institution.query('Users/User/Email').value('.','varchar(50)') As Email,

    tblXML.Institution.query('Users/User/BusinessRoleGuid').value('.','uniqueidentifier') As BusinessRoleGuid

    FROM @UserXML.nodes('//Institution') tblXML (Institution)

    How can I get the data is this format

    InstitutionGUIDEmailBusinessRoleGUID

    0657F7ED-58CA-4A3D-8393-B9A3DD315319A@Test.com 0657F7ED-58CA-4A3D-8393-B9A3DD31531

    0657F7ED-58CA-4A3D-8393-B9A3DD315319b@Test.com 1657F7ED-58CA-4A3D-8393-B9A3DD315319

    Kindest Regards,

    Amit Lohia

  • June 16, 2008 at 4:40 pm

    #829537

    XML>

    Institution Id="0657F7ED-58CA-4A3D-8393-B9A3DD315319">

    Users>

    User>

    Email>A@test.com

    BusinessRoleGuid>0657F7ED-58CA-4A3D-8393-B9A3DD315319

    /User>

    User>

    Email>B@test.com

    BusinessRoleGuid>1657F7ED-58CA-4A3D-8393-B9A3DD315319

    /User>

    /Users>

    /Institution>

    /XML

    Here is the example, cannot post with "<"

    Kindest Regards,

    Amit Lohia

  • June 16, 2008 at 8:55 pm

    #829573

    Try this:

    [font="Courier New"]DECLARE @x XML

    SET @x = '<XML>

      <Institution Id="0657F7ED-58CA-4A3D-8393-B9A3DD315319">

        <Users>

          <User>

            <Email>A@test.com</Email>

            <BusinessRoleGuid>0657F7ED-58CA-4A3D-8393-B9A3DD315319</BusinessRoleGuid>

          </User>

          <User>

            <Email>B@test.com</Email>

            <BusinessRoleGuid>1657F7ED-58CA-4A3D-8393-B9A3DD315319</BusinessRoleGuid>

          </User>

      </Users>

    </Institution>

    </XML>'

    SELECT T.Loc.value('(/XML/Institution/@Id)[1]','uniqueidentifier') AS [Institution]

    , T.Loc.value('(/XML/Institution/Users/User/Email)[1]','varchar(128)') AS [Email]

    , T.Loc.value('(/XML/Institution/Users/User/BusinessRoleGuid)[1]','uniqueidentifier') AS [BusinessRoleGuid]

    FROM @x.nodes('/XML/Institution/Users/User') AS T(Loc )

    [/font]

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 17, 2008 at 4:07 pm

    #830104

    It will return the same result set

    0657F7ED-58CA-4A3D-8393-B9A3DD315319A@test.com0657F7ED-58CA-4A3D-8393-B9A3DD315319

    I think it is because of [1]

    Kindest Regards,

    Amit Lohia

  • June 17, 2008 at 4:20 pm

    #830106

    You are right, but if you look at the result set it give you the same recordset. Second row should be b@test.com

    Kindest Regards,

    Amit Lohia

  • June 17, 2008 at 4:35 pm

    #830113

    This works, though I honestly have no idea why:

    select T.Loc.value('(/XML/Institution/@Id)[1]','uniqueidentifier') AS [Institution]

    , t.Loc.query('.').value('(/User/Email)[1]','varchar(128)') AS [Email]

    , t.Loc.query('.').value('(/User/BusinessRoleGuid)[1]','uniqueidentifier') AS [BusinessRoleGuid]

    FROM @x.nodes('/XML/Institution/Users/User') AS T(Loc )

    🙁 *sigh* XML queries just remain so much black magic to me. I simply cannot figure out why one of these works and another does not. It seems completely illogical and random. :angry:

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 17, 2008 at 4:58 pm

    #830117

    Slightly simpler...

    select T.Loc.value('../../@Id','uniqueidentifier') AS [Institution]

    , t.Loc.value('./Email[1]','varchar(128)') AS [Email]

    , t.Loc.value('./BusinessRoleGuid[1]','uniqueidentifier') AS [BusinessRoleGuid]

    FROM @x.nodes('/XML/Institution/Users/User') AS T(Loc )

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 22, 2008 at 5:04 am

    #846280

    this is good as long as the XML is untyped. What if the XML is in a XML DB Column ? for example AdventureWorks DB's HumarResuource.JobCandidate.Resume column ??

    Thanks & Regards,

    Venkat.

  • April 13, 2012 at 11:49 am

    #1473387

    This looks good but what if you have more than 1, then how would I make the program dynamic to pull 1, 2,3 4, elements...

Viewing 9 posts - 1 through 8 (of 8 total)

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