Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: OPENXML question

    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?

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: OPENXML question

    If you're not limited to using OPENXML and you're developing in SS 2005 and above, you may want to consider the following:

    Code:
    declare @xd xml;
    
    set @xd = N'<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 @xd.value('(//Limits[LimitType="LOB" or LimitType="LOU"]/LimitAmt)[1]','int');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •