OpenXML question

[scottcabral1979]

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?

 

[gmmastros]

The problem here is that the XML is ugly. If you can, I would encourage you to change the XML to something like this:

<Root>
	<Policy>
		<PolicyNumber>123456</PolicyNumber>
	</Policy>
	<Limits>
		<Limit Type="LOB">1000</Limit>
		<Limit Type="PP">5000</Limit>
		<Limit Type="LOU">5000</Limit>
	</Limits>
</Root>

or 

<Root>
	<Policy>
		<PolicyNumber>123456</PolicyNumber>
	</Policy>
	<Limits>
		<Limit Type="LOB" Amt="1000" />
		<Limit Type="PP" Amt="5000" />
		<Limit Type="LOU" Amt="5000" />
	</Limits>
</Root>

The problem with your current XML is that it requires a special ordering where a LimitAmt is follows a LimitType. The order of the nodes in an XML document should not matter. With the alternative XML structure I suggested, the order doesn't matter.

If you cannot change the XML structure, you may try the following query.

declare @XML 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	Type, Amount
From    (
        Select @XML.value('(/Root/Limits/LimitType)[1]', 'varchar(50)') As Type,
               @XML.value('(/Root/Limits/LimitAmt)[1]', 'varchar(50)') As Amount

        Union All

	Select @XML.value('(/Root/Limits/LimitType)[2]', 'varchar(50)'),
               @XML.value('(/Root/Limits/LimitAmt)[2]', 'varchar(50)')

        Union All

	Select @XML.value('(/Root/Limits/LimitType)[3]', 'varchar(50)'),
               @XML.value('(/Root/Limits/LimitAmt)[3]', 'varchar(50)')

        Union All

        Select @XML.value('(/Root/Limits/LimitType)[4]', 'varchar(50)'),
               @XML.value('(/Root/Limits/LimitAmt)[4]', 'varchar(50)')
        ) As Data
Where	Type In ('LOB','LOU')

Notice that the code relies on absolution positioning. I added a 4th possible item just to show that you can have additional items in the query without causing problems. The real problem is that your input XML data could have any number of items in the Limits node so you would need to hard code this query to accommodate the most number of nodes that you would every have.

This is problematic though because you cannot predict the future and therefore cannot predict the number of hard coded values you should use.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom