OpenXML question - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

OpenXML question

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 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

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 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

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 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

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-06-15 : 01:34:31

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

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>
';

-- 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[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources