February 17, 2017 at 9:35 am
Hello all,
I have the following xml and I'm trying to build a query that will output ids of both familyid element and memberid element under each family node. Is it possible using sql?
DECLARE @XML XML;
SET @XML =
N'
<root>
<families>
<family>
<Familyid>F1</Familyid>
<FamilyMemberDetails>
<Members>
<Familyid>F1</Familyid>
<Id>M1</Id>
<FirstName>John</FirstName>
</Members>
<Members>
<Familyid>F1</Familyid>
<Id>M2</Id>
<FirstName>Lewis</FirstName>
</Members>
</FamilyMemberDetails>
</family>
<family>
<Familyid>F2</Familyid>
<FamilyMemberDetails>
<Members>
<Familyid>F2</Familyid>
<Id>M1</Id>
<FirstName>Chris</FirstName>
</Members>
<Members>
<Familyid>F2</Familyid>
<Id>M2</Id>
<FirstName>Linda</FirstName>
</Members>
</FamilyMemberDetails>
</family>
</families>
</root>';
SELECT @XML;
FamilyID MemberID
-----------------------
F1 M1
-----------------------
F1 M2
-----------------------
F2 M1
-----------------------
F2 M2
-----------------------
February 18, 2017 at 12:58 am
Two methods which return the same results
😎DECLARE @XML XML;
SET @XML =
N'<root>
<families>
<family>
<Familyid>F1</Familyid>
<FamilyMemberDetails>
<Members>
<Familyid>F1</Familyid>
<Id>M1</Id>
<FirstName>John</FirstName>
</Members>
<Members>
<Familyid>F1</Familyid>
<Id>M2</Id>
<FirstName>Lewis</FirstName>
</Members>
</FamilyMemberDetails>
</family>
<family>
<Familyid>F2</Familyid>
<FamilyMemberDetails>
<Members>
<Familyid>F2</Familyid>
<Id>M1</Id>
<FirstName>Chris</FirstName>
</Members>
<Members>
<Familyid>F2</Familyid>
<Id>M2</Id>
<FirstName>Linda</FirstName>
</Members>
</FamilyMemberDetails>
</family>
</families>
</root>';
---------------------------------------------------------------------------------------
SELECT
FAMILY.DATA.value('(Familyid/text())[1]','VARCHAR(10)') AS FamilyID
,FAMILY.DATA.value('(Id/text())[1]' ,'VARCHAR(10)') AS MemberID
FROM @XML.nodes('root/families/family/FamilyMemberDetails/Members') AS FAMILY(DATA);
---------------------------------------------------------------------------------------
SELECT
FAMILY.DATA.value('(Familyid/text())[1]' ,'VARCHAR(10)') AS FamilyID
,MEMBERS.DATA.value('(Id/text())[1]' ,'VARCHAR(10)') AS MemberID
FROM @XML.nodes('root/families/family') AS FAMILY(DATA)
CROSS APPLY FAMILY.DATA.nodes('FamilyMemberDetails/Members') MEMBERS(DATA);
---------------------------------------------------------------------------------------
OutputFamilyID MemberID
---------- ----------
F1 M1
F1 M2
F2 M1
F2 M2
February 21, 2017 at 2:03 am
Eirikur Eiriksson - Saturday, February 18, 2017 12:58 AMTwo methods which return the same results
😎
Hello Eirikur,
Both methods deliver the same result (for the example code), the second script is longer and looks more complex (to me). So can you explain why the second method ?
I am trying to learn from you anwser, thanks.
Ben
February 21, 2017 at 7:47 am
Eirikur Eiriksson - Saturday, February 18, 2017 12:58 AMTwo methods which return the same results
😎DECLARE @XML XML;SET @XML =
N'<root>
<families>
<family>
<Familyid>F1</Familyid>
<FamilyMemberDetails>
<Members>
<Familyid>F1</Familyid>
<Id>M1</Id>
<FirstName>John</FirstName>
</Members>
<Members>
<Familyid>F1</Familyid>
<Id>M2</Id>
<FirstName>Lewis</FirstName>
</Members>
</FamilyMemberDetails>
</family>
<family>
<Familyid>F2</Familyid>
<FamilyMemberDetails>
<Members>
<Familyid>F2</Familyid>
<Id>M1</Id>
<FirstName>Chris</FirstName>
</Members>
<Members>
<Familyid>F2</Familyid>
<Id>M2</Id>
<FirstName>Linda</FirstName>
</Members>
</FamilyMemberDetails>
</family>
</families>
</root>';
---------------------------------------------------------------------------------------
SELECT
FAMILY.DATA.value('(Familyid/text())[1]','VARCHAR(10)') AS FamilyID
,FAMILY.DATA.value('(Id/text())[1]' ,'VARCHAR(10)') AS MemberID
FROM @XML.nodes('root/families/family/FamilyMemberDetails/Members') AS FAMILY(DATA);
---------------------------------------------------------------------------------------
SELECT
FAMILY.DATA.value('(Familyid/text())[1]' ,'VARCHAR(10)') AS FamilyID
,MEMBERS.DATA.value('(Id/text())[1]' ,'VARCHAR(10)') AS MemberID
FROM @XML.nodes('root/families/family') AS FAMILY(DATA)
CROSS APPLY FAMILY.DATA.nodes('FamilyMemberDetails/Members') MEMBERS(DATA);
---------------------------------------------------------------------------------------Output
FamilyID MemberID
---------- ----------
F1 M1
F1 M2
F2 M1
F2 M2
='font-size:9.0pt;mso-bidi-font-size:11.0pt;line-height:107%;font-family:"courier>
Thanks..it worked!
February 21, 2017 at 9:03 am
ben.brugman - Tuesday, February 21, 2017 2:03 AMEirikur Eiriksson - Saturday, February 18, 2017 12:58 AMTwo methods which return the same results
😎Hello Eirikur,
Both methods deliver the same result (for the example code), the second script is longer and looks more complex (to me). So can you explain why the second method ?I am trying to learn from you anwser, thanks.
Ben
The difference is that the second method goes first on the Family node level and then for each of those it iterates through all the member child nodes. This is useful if i.e. the family ID is only present at the parent level and not the child level. Performance wise those two methods are almost identical.
😎
February 23, 2017 at 6:12 am
Eirikur Eiriksson - Tuesday, February 21, 2017 9:03 AMThe difference is that the second method goes first on the Family node level and then for each of those it iterates through all the member child nodes. This is useful if i.e. the family ID is only present at the parent level and not the child level. Performance wise those two methods are almost identical.
😎
Thanks, this is usefull to know and applicable for many other examples. Thanks for extending the anwser beyond the question, usefull.
Thanks,
Ben
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply