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