Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Reading from XML

Jawab Reading from XML

  • Monday, February 20, 2012 8:10 AM
     
     
    Sign In to Vote
    0

    Hi,

    I have following XML.

    <pc:PolicyContainer xmlns:pc="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CaseId="51001">
      <Policy xmlns="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CreationTime="2012-02-20T06:18:58.0043345Z">
        <SystemData CreationTime="2012-02-20T06:18:59.0043345Z">
          <DestinationService>Automated</DestinationService>
        </SystemData>
        <Billing CreationTime="2012-02-20T06:31:46.2609995Z">
          <CurrentPlan CreationTime="2012-02-20T06:19:22Z" IsSelected="false">
            <PlanCode>1EA</PlanCode>
            <Percentage>100</Percentage>
            <DownPayment CreationTime="2012-02-20T06:19:23Z">
              <Type>Other</Type>
              <Number>0</Number>
              <Amount>169</Amount>
               <Fee>0.00</Fee>
              <ServiceCharge>0</ServiceCharge>
              <Due>2012-02-21T00:00:00</Due>
            </DownPayment>
          </CurrentPlan>
        </Billing>
      </Policy>
    </pc:PolicyContainer>

    I have used the following query to read the values, but its throwing NULL.

    select quote.value('(//Policy/SystemData/DestinationService)[1]', 'VARCHAR(100)') from dbo.PQI where id=2

    Please suggest. And also please suggest the way to read the DownPayment  value.

    Thanks


    Porus

     

All Replies

  • Monday, February 20, 2012 9:39 AM
     
     Answered Has Code
    Sign In to Vote
    0

    hi,

    you need to specify the namespace when using XPath expressions. The simple solution is using the WITH XMLNAMESPACES declaration, e.g.

    DECLARE @Sample XML = '
<pc:PolicyContainer xmlns:pc="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CaseId="51001">
  <Policy xmlns="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CreationTime="2012-02-20T06:18:58.0043345Z">
    <SystemData CreationTime="2012-02-20T06:18:59.0043345Z">
      <DestinationService>Automated</DestinationService>
    </SystemData>
    <Billing CreationTime="2012-02-20T06:31:46.2609995Z">
      <CurrentPlan CreationTime="2012-02-20T06:19:22Z" IsSelected="false">
        <PlanCode>1EA</PlanCode>
        <Percentage>100</Percentage>
        <DownPayment CreationTime="2012-02-20T06:19:23Z">
          <Type>Other</Type>
          <Number>0</Number>
          <Amount>169</Amount>
           <Fee>0.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-02-21T00:00:00</Due>
        </DownPayment>
      </CurrentPlan>
    </Billing>
  </Policy>
</pc:PolicyContainer>
' ;

WITH XMLNAMESPACES ( 'http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd' AS pc)  
SELECT  Policy.value('(pc:SystemData/pc:DestinationService)[1]', 'VARCHAR(100)') AS DestinationService ,
        Policy.value('(pc:Billing/pc:CurrentPlan/pc:DownPayment/@CreationTime)[1]', 'VARCHAR(100)') AS DownPaymentCreationTime ,
        Policy.value('(pc:Billing/pc:CurrentPlan/pc:DownPayment/pc:Type)[1]', 'VARCHAR(100)') AS DownPaymentType
FROM    @Sample.nodes('//pc:PolicyContainer/pc:Policy') PolicyContainer ( Policy ) ;

    • Edited by Stefan HoffmannMVP Monday, February 20, 2012 9:47 AM Better example.
    • Marked As Answer by Aneeshporus Monday, February 20, 2012 1:01 PM
    •  
     
  • Monday, February 20, 2012 1:20 PM
     
      Has Code
    Sign In to Vote
    0

    Thanks Stefan...And I need help for the following also please:

    I would like to read the following data and number of coverages alwys changes. I want to capture as many as present in XML and store in DB.

    Please advice. Want to read/stre Time,Mnemonic,Coverage code,id,Order,IsVehicleRelated,...all properties of XML.

    <pc:PolicyContainer xmlns:pc="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CaseId="51001">
  <Policy xmlns="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CreationTime="2012-02-20T06:18:58.0043345Z">

    <Coverages CreationTime="2012-02-20T06:19:33.0043345Z">

     <Coverage CreationTime="2012-02-20T06:19:34.0043345Z" Mnemonic="BI" CoverageCode="001" id="X550efb17-58ea-4442-bfea-29995a054f2e">

        

            <Order>1</Order>
            <IsVehicleRelated>false</IsVehicleRelated>
            <ShortName>BI</ShortName>
            <LongName>Bodily Injury Liability(BI)</LongName>
            <Limit CreationTime="2012-02-20T06:19:35.0043345Z" Mnemonic="50/100" CoverageCode="00051">
              <AmountPerPerson>50000</AmountPerPerson>
              <AmountPerOcurrence>100000</AmountPerOcurrence>
            </Limit>
            <Deductible CreationTime="2012-02-20T06:19:36.0043345Z" Mnemonic="" CoverageCode="000">
              <Amount>0</Amount>
            </Deductible>
          </Coverage>
          <Coverage CreationTime="2012-02-20T06:19:37.0043345Z" Mnemonic="PD" CoverageCode="002" id="Xd2944933-013e-4e56-95e1-fe0fa7ffc2fb">
            <Order>2</Order>
            <IsVehicleRelated>false</IsVehicleRelated>
            <ShortName>PD</ShortName>
            <LongName>Property Damage Liability(PD)</LongName>
            <Limit CreationTime="2012-02-20T06:19:38.0043345Z" Mnemonic="25M" CoverageCode="25000">
              <AmountPerPerson>0</AmountPerPerson>
              <AmountPerOcurrence>25000</AmountPerOcurrence>
            </Limit>
            <Deductible CreationTime="2012-02-20T06:19:39.0043345Z" Mnemonic="" CoverageCode="000">
              <Amount>0</Amount>
            </Deductible>
          </Coverage>
          <Coverage CreationTime="2012-02-20T06:19:40.0043345Z" Mnemonic="MED" CoverageCode="003" id="X2326e3ea-9131-4938-9be6-0764ecf43dad">
            <Order>3</Order>
            <IsVehicleRelated>false</IsVehicleRelated>
            <ShortName>MED</ShortName>
            <LongName>Medical Payments(MED)</LongName>
            <Limit CreationTime="2012-02-20T06:19:41.0043345Z" Mnemonic="REJ" CoverageCode="00117">
              <AmountPerPerson>0</AmountPerPerson>
              <AmountPerOcurrence>0</AmountPerOcurrence>
            </Limit>
            <Deductible CreationTime="2012-02-20T06:19:42.0043345Z" Mnemonic="" CoverageCode="REJ">
              <Amount>0</Amount>
            </Deductible>
          </Coverage>
          <Coverage CreationTime="2012-02-20T06:19:43.0043345Z" Mnemonic="UM&amp;UND" CoverageCode="086" id="X1f8e5dce-574d-4c4c-8356-863b139a6e9f">
            <Order>4</Order>
            <IsVehicleRelated>false</IsVehicleRelated>
            <ShortName>UM&amp;UND</ShortName>
            <LongName>Uninsured &amp;Underinsured Motorist(UM&amp;UND)</LongName>
            <Limit CreationTime="2012-02-20T06:19:44.0043345Z" Mnemonic="50/100" CoverageCode="00051">
              <AmountPerPerson>50000</AmountPerPerson>
              <AmountPerOcurrence>100000</AmountPerOcurrence>
            </Limit>
            <Deductible CreationTime="2012-02-20T06:19:45.0043345Z" Mnemonic="" CoverageCode="000">
              <Amount>0</Amount>
            </Deductible>
          </Coverage>
          <Coverage CreationTime="2012-02-20T06:19:46.0043345Z" Mnemonic="UIMPD" CoverageCode="092" id="X6a5585a2-5f97-4c20-9c26-b6f6067ace40">
            <Order>5</Order>
            <IsVehicleRelated>false</IsVehicleRelated>
            <ShortName>UIMPD</ShortName>
            <LongName>Uninsured &amp;Underinsured Motorist Property Damage(UIMPD)</LongName>
            <Limit CreationTime="2012-02-20T06:19:47.0043345Z" Mnemonic="25M" CoverageCode="25000">
              <AmountPerPerson>0</AmountPerPerson>
              <AmountPerOcurrence>25000</AmountPerOcurrence>
            </Limit>
            <Deductible CreationTime="2012-02-20T06:19:48.0043345Z" Mnemonic="250" CoverageCode="006">
              <Amount>25000</Amount>
            </Deductible>
          </Coverage>
          <Coverage CreationTime="2012-02-20T06:19:49.0043345Z" Mnemonic="COMP" CoverageCode="005" id="X674fb844-ee0d-4bdb-b347-15a20765bfe5">
            <Order>6</Order>
            <IsVehicleRelated>true</IsVehicleRelated>
            <ShortName>COMP</ShortName>
            <LongName>Comprehensive(COMP)</LongName>
            <Limit CreationTime="2012-02-20T06:19:50.0043345Z" Mnemonic="REJ" CoverageCode="00117">
              <AmountPerPerson>0</AmountPerPerson>
              <AmountPerOcurrence>0</AmountPerOcurrence>
            </Limit>
            <Deductible CreationTime="2012-02-20T06:19:51.0043345Z" Mnemonic="" CoverageCode="REJ">
              <Amount>0</Amount>
            </Deductible>
          </Coverage>
          <Coverage CreationTime="2012-02-20T06:19:52.0043345Z" Mnemonic="COLL" CoverageCode="007" id="X11c5437e-5c9d-4341-aa72-68a708cb9d23">
            <Order>7</Order>
            <IsVehicleRelated>true</IsVehicleRelated>
            <ShortName>COLL</ShortName>
            <LongName>Collision(COLL)</LongName>
            <Limit CreationTime="2012-02-20T06:19:53.0043345Z" Mnemonic="REJ" CoverageCode="00117">
              <AmountPerPerson>0</AmountPerPerson>
              <AmountPerOcurrence>0</AmountPerOcurrence>
            </Limit>
            <Deductible CreationTime="2012-02-20T06:19:54.0043345Z" Mnemonic="" CoverageCode="REJ">
              <Amount>0</Amount>
            </Deductible>
          </Coverage>
        </Coverages>

      </Policy>

    </pc:PolicyContainer>

    Thanks in advance.

    Porus

     
  • Monday, February 20, 2012 1:34 PM
     
     Answered Has Code
    Sign In to Vote
    0

    E.g. a smiplified version:

    DECLARE @Sample XML = '
<pc:PolicyContainer xmlns:pc="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CaseId="51001">
  <Policy xmlns="http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd" CreationTime="2012-02-20T06:18:58.0043345Z">
    <Coverages CreationTime="2012-02-20T06:19:33.0043345Z">
      <Coverage CreationTime="2012-02-20T06:19:49.0043345Z" Mnemonic="COMP" CoverageCode="005" id="X674fb844-ee0d-4bdb-b347-15a20765bfe5">
        <Order>6</Order>
        <IsVehicleRelated>true</IsVehicleRelated>
        <ShortName>COMP</ShortName>
        <LongName>Comprehensive(COMP)</LongName>
        <Limit CreationTime="2012-02-20T06:19:50.0043345Z" Mnemonic="REJ" CoverageCode="00117">
          <AmountPerPerson>0</AmountPerPerson>
          <AmountPerOcurrence>0</AmountPerOcurrence>
        </Limit>
        <Deductible CreationTime="2012-02-20T06:19:51.0043345Z" Mnemonic="" CoverageCode="REJ">
          <Amount>0</Amount>
        </Deductible>
      </Coverage>
      <Coverage CreationTime="2012-02-20T06:19:52.0043345Z" Mnemonic="COLL" CoverageCode="007" id="X11c5437e-5c9d-4341-aa72-68a708cb9d23">
        <Order>7</Order>
        <IsVehicleRelated>true</IsVehicleRelated>
        <ShortName>COLL</ShortName>
        <LongName>Collision(COLL)</LongName>
        <Limit CreationTime="2012-02-20T06:19:53.0043345Z" Mnemonic="REJ" CoverageCode="00117">
          <AmountPerPerson>0</AmountPerPerson>
          <AmountPerOcurrence>0</AmountPerOcurrence>
        </Limit>
        <Deductible CreationTime="2012-02-20T06:19:54.0043345Z" Mnemonic="" CoverageCode="REJ">
          <Amount>0</Amount>
        </Deductible>
      </Coverage>
    </Coverages>
  </Policy>
</pc:PolicyContainer>
' ;

WITH XMLNAMESPACES ( 'http://biztalk.iss.net/schemas/policyData/PolicyContainer.xsd' AS pc)  
SELECT  Coverage.query('.') ,
        Coverage.value('@Mnemonic', 'NVARCHAR(255)') AS CoverageMnemonic ,
        Coverage.value('pc:Order[1]', 'INT') AS CoverageOrder ,
        Coverage.value('pc:Limit[1]/@Mnemonic', 'NVARCHAR(255)') AS LimitMnemonic ,
        Coverage.value('pc:Limit[1]/pc:AmountPerPerson[1]', 'NVARCHAR(255)') AS LimitAmountPerPerson
FROM    @Sample.nodes('//pc:PolicyContainer/pc:Policy/pc:Coverages/pc:Coverage') Coverages ( Coverage ) ;

    • Marked As Answer by Aneeshporus Tuesday, February 21, 2012 11:25 AM
    •  
     
  • Tuesday, February 21, 2012 11:28 AM
     
     
    Sign In to Vote
    0

    Thanks.

    Can anybody please help in reading following data also.

    <pay>
      <PayPlan CreationTime="2012-02-20T10:00:27Z" IsSelected="false">
        <PlanCode>4EB</PlanCode>
        <Installment CreationTime="2012-02-20T10:00:29Z">
          <Type>Other</Type>
          <Number>1</Number>
          <Amount>56.25</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-04-23T00:00:00</Due>
        </Installment>
        <Installment CreationTime="2012-02-20T10:00:30Z">
          <Type>Other</Type>
          <Number>2</Number>
          <Amount>56.25</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-06-23T00:00:00</Due>
        </Installment>
        <Installment CreationTime="2012-02-20T10:00:31Z">
          <Type>Other</Type>
          <Number>3</Number>
          <Amount>56.25</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-08-23T00:00:00</Due>
        </Installment>
      </PayPlan>
      <PayPlan CreationTime="2012-02-20T10:00:32Z" IsSelected="false">
        <PlanCode>6EB</PlanCode>
        <Installment CreationTime="2012-02-20T10:00:34Z">
          <Type>Other</Type>
          <Number>1</Number>
          <Amount>33.75</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-04-23T00:00:00</Due>
        </Installment>
        <Installment CreationTime="2012-02-20T10:00:35Z">
          <Type>Other</Type>
          <Number>2</Number>
          <Amount>33.75</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-06-23T00:00:00</Due>
        </Installment>
        <Installment CreationTime="2012-02-20T10:00:36Z">
          <Type>Other</Type>
          <Number>3</Number>
          <Amount>33.75</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-08-23T00:00:00</Due>
        </Installment>
        <Installment CreationTime="2012-02-20T10:00:37Z">
          <Type>Other</Type>
          <Number>4</Number>
          <Amount>33.75</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-10-23T00:00:00</Due>
        </Installment>
        <Installment CreationTime="2012-02-20T10:00:38Z">
          <Type>Other</Type>
          <Number>5</Number>
          <Amount>33.75</Amount>
          <Fee>5.00</Fee>
          <ServiceCharge>0</ServiceCharge>
          <Due>2012-12-23T00:00:00</Due>
        </Installment>
      </PayPlan>
    </pay>

    each pay plan has multiple installments.  I need payplan value with corresponding installment vallues like

    Payplan|Installtype|InstallOther|...

    Please suggest

    Thanks

    Porus