Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
scabral7
Yak Posting Veteran
57 Posts |
Posted - 2013-06-14 : 11:10:35
|
Hi,I have the following xml file:<Root> <Policy> <PolicyNumber>123456</PolicyNumber> </Policy> <Limits> <LimitType>LOB</LimitType> <LimitAmt>1000</LimitAmt> <LimitType>PP</LimitType> <LimitAmt>5000</LimitAmt> <LimitType>LOU</LimitType> <LimitAmt>5000</LimitAmt> </Limits></Root>I only want to select The LimitAmt tag if the LimitType=LOB or LOU. I never want PP. Also, LOB or LOU may not be in every Policy, so it should be null. Also, the order could be different (ie LOB 2nd or 3rd etc...)How do i write the OPENXML query to get just those 2 elements from the limits section of the xml file? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 15:02:13
|
your xml is not well formed. In current form there's no way you can find out related LimitType and LimitAmti think xml should be likwdeclare @x xml = '<Root><Policy><PolicyNumber>123456</PolicyNumber></Policy><Limits><LimitInfo><LimitType>LOB</LimitType><LimitAmt>1000</LimitAmt></LimitInfo><LimitInfo><LimitType>PP</LimitType><LimitAmt>5000</LimitAmt></LimitInfo><LimitInfo><LimitType>LOU</LimitType><LimitAmt>5000</LimitAmt></LimitInfo></Limits></Root>'SELECT t.u.value('./LimitAmt[1]','int') AS LimitAmountFROM @x.nodes('/Root/Limits/LimitInfo[./LimitType="LOB" or ./LimitType="LOU"]')t(u) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2013-06-14 : 15:17:47
|
if you can not change the xml structure , like visakh said,declare @xml as xmlset @xml='<Root> <Policy> <PolicyNumber>123456</PolicyNumber> </Policy> <Limits> <LimitType>LOB</LimitType> <LimitAmt>1000</LimitAmt> <LimitType>PP</LimitType> <LimitAmt>5000</LimitAmt> <LimitType>LOU</LimitType> <LimitAmt>5000</LimitAmt> </Limits></Root>'selectt.u.value('LimitType[1]','nvarchar(30)') as [LimitType],t.u.value('LimitAmt[1]','nvarchar(30)') as [LimitAmt]from @xml.nodes('Root/Limits') as t(u)where t.u.value('LimitType[1]','nvarchar(30)') in ('LOB','LOU')union allselectt.u.value('LimitType[2]','nvarchar(30)') as [LimitType],t.u.value('LimitAmt[2]','nvarchar(30)') as [LimitAmt]from @xml.nodes('Root/Limits') as t(u)where t.u.value('LimitType[2]','nvarchar(30)') in ('LOB','LOU')union allselectt.u.value('LimitType[3]','nvarchar(30)') as [LimitType],t.u.value('LimitAmt[3]','nvarchar(30)') as [LimitAmt]from @xml.nodes('Root/Limits') as t(u)where t.u.value('LimitType[3]','nvarchar(30)') in ('LOB','LOU') Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 15:26:08
|
quote: Originally posted by stepson if you can not change the xml structure , like visakh said,declare @xml as xmlset @xml='<Root> <Policy> <PolicyNumber>123456</PolicyNumber> </Policy> <Limits> <LimitType>LOB</LimitType> <LimitAmt>1000</LimitAmt> <LimitType>PP</LimitType> <LimitAmt>5000</LimitAmt> <LimitType>LOU</LimitType> <LimitAmt>5000</LimitAmt> </Limits></Root>'selectt.u.value('LimitType[1]','nvarchar(30)') as [LimitType],t.u.value('LimitAmt[1]','nvarchar(30)') as [LimitAmt]from @xml.nodes('Root/Limits') as t(u)where t.u.value('LimitType[1]','nvarchar(30)') in ('LOB','LOU')union allselectt.u.value('LimitType[2]','nvarchar(30)') as [LimitType],t.u.value('LimitAmt[2]','nvarchar(30)') as [LimitAmt]from @xml.nodes('Root/Limits') as t(u)where t.u.value('LimitType[2]','nvarchar(30)') in ('LOB','LOU')union allselectt.u.value('LimitType[3]','nvarchar(30)') as [LimitType],t.u.value('LimitAmt[3]','nvarchar(30)') as [LimitAmt]from @xml.nodes('Root/Limits') as t(u)where t.u.value('LimitType[3]','nvarchar(30)') in ('LOB','LOU') Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb
Not auto scalablewhat if the number of LimitType and Amt nodes vary and cant be determined before hand?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2013-06-15 : 01:34:31
|
quote: Not auto scalablewhat if the number of LimitType and Amt nodes vary and cant be determined before hand?
I agree with you, it's not auto scalableit's an ugly solution.I went from the ideea that he can not change the xml and have 3items in LimitsCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-15 : 02:42:30
|
[code]DECLARE @Data XML = ' <Root> <Policy> <PolicyNumber>123456</PolicyNumber> </Policy> <Limits> <LimitType>LOB</LimitType> <LimitAmt>1000</LimitAmt> <LimitType>PP</LimitType> <LimitAmt>5000</LimitAmt> <xLimitType>LOU</xLimitType> <xLimitAmt>2500</xLimitAmt> <xLimitType>LOU</xLimitType> <xLimitAmt>5000</xLimitAmt> </Limits> </Root> ';-- SwePesoWITH cteRaw(LocalName, Data, rn)AS ( SELECT n.value('local-name(.)', 'VARCHAR(MAX)') AS LocalName, n.value('.', 'VARCHAR(MAX)') AS Data, ROW_NUMBER() OVER (ORDER BY d.n) - 1 AS rn FROM @Data.nodes('/Root/Limits/*') AS d(n) UNION ALL SELECT 'LimitType', 'LOB', -4 UNION ALL SELECT 'LimitType', 'LOU', -2), cteSourceAS ( SELECT MAX(CASE WHEN rn % 2 = 0 THEN Data ELSE NULL END) AS Data, MAX(CASE WHEN rn % 2 = 1 THEN Data ELSE NULL END) AS Value FROM cteRaw WHERE LocalName IN ('LimitType', 'LimitAmt') GROUP BY rn / 2)SELECT Data, MAX(Value) AS ValueFROM cteSourceWHERE Data IN ('LOB', 'LOU')GROUP BY Data[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|