SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 OpenXML question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scabral7
Yak Posting Veteran

57 Posts

Posted - 06/14/2013 :  11:10:35  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/14/2013 :  15:02:13  Show Profile  Reply with Quote
your xml is not well formed. In current form there's no way you can find out related LimitType and LimitAmt

i think xml should be likw



declare @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 LimitAmount
FROM @x.nodes('/Root/Limits/LimitInfo[./LimitType="LOB" or ./LimitType="LOU"]')t(u)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/14/2013 15:04:36
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 06/14/2013 :  15:17:47  Show Profile  Reply with Quote
if you can not change the xml structure , like visakh said,


declare @xml as xml
set @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>'

select
t.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 all

select
t.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 all

select
t.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 mut
sabinWeb
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/14/2013 :  15:26:08  Show Profile  Reply with Quote
quote:
Originally posted by stepson

if you can not change the xml structure , like visakh said,


declare @xml as xml
set @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>'

select
t.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 all

select
t.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 all

select
t.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 mut
sabinWeb


Not auto scalable
what if the number of LimitType and Amt nodes vary and cant be determined before hand?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 06/15/2013 :  01:34:31  Show Profile  Reply with Quote
quote:

Not auto scalable
what if the number of LimitType and Amt nodes vary and cant be determined before hand?



I agree with you, it's not auto scalable
it's an ugly solution.I went from the ideea that he can not change the xml and have 3items in Limits



Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 06/15/2013 :  02:42:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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>
			';

-- SwePeso
WITH 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
), cteSource
AS (
	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 Value
FROM		cteSource
WHERE		Data IN ('LOB', 'LOU')
GROUP BY	Data



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000