none
how to get the iterated xml node value and insert into sql tables RRS feed

  • Question

  • <?xml version="1.0" encoding="utf-8"?>
    <Root>
      <Array-DataNode_1>
        <DataNode_1>
          <Coupon1>nocharg</Coupon1>
          <Coupon1_Price>NO-CHARGE</Coupon1_Price>
          <Coupon1_PriceType>5</Coupon1_PriceType>
          <Coupon1_VariableDisclaimer xml:space="preserve">Valid for VIN ending in AE549168. </Coupon1_VariableDisclaimer>
          <Coupon1_ExpDate />
          <Coupon2>accmat</Coupon2>
          <Coupon2_Price>10</Coupon2_Price>
          <Coupon2_PriceType>3</Coupon2_PriceType>
          <Coupon2_VariableDisclaimer xml:space="preserve">Valid for VIN ending in AE549168. </Coupon2_VariableDisclaimer>
          <Coupon2_ExpDate>12/03/2010</Coupon2_ExpDate>
          <Recom_Due1 />
          <Recom_Due2 />
          <Recom_Due3 />
          <Recom_Due4 />
          <Ltr_Date>20110215</Ltr_Date>
        </DataNode_1>

    </Array-DataNode_1>

    </Root>

    the above specified sample data is going to import in sql tables

     

     

    the coupon node is iterated i don't know how to parse and insert into sql tables. im using the openxml method to import the xml file

    i want the output as table showed below

     

    id   couponcategory    price type        price

    1     nocharg              5                   NO-CHARGE    -- coupon1 node details

    2     accmat               3                    10                -- coupon2 node details

     

    Query:


        INSERT INTO TouchPointCoupon(FK_touchpoint, coupon_category, price_type, price)
               

                    SELECT          
                                        XMLInput.nodevalue1,
                                        XMLInput.nodevalue2,                   
                                        XMLInput.nodevalue3,
                                                                           
                                       

                    FROM OPENXML(@XMLHandle,'Root/Array-DataNode_1/DataNode_1',2)
                    WITH
                    (       

                    node1 datatype,

                  node2 datatype,

                   node3 datatype         

    )as XMLInput

     

    please tell the solution ..............


    Sudhesh. G
    http://gurucoders.blogspot.com
    Thursday, February 17, 2011 2:19 PM

Answers

  • If using OPENXML solution:

    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @coupons
    
    INSERT INTO TouchPointCoupon(coupon_category, price_type, price)   
    	SELECT   
    		XMLInput.Coupon1,
    		XMLInput.Coupon1_PriceType,     
    		XMLInput.Coupon1_Price
    		FROM OPENXML(@idoc,'/Root/Array-DataNode_1/DataNode_1',2)
    		WITH
    		(  
    			Coupon1 VARCHAR(50),
    			Coupon1_PriceType VARCHAR(50),
    			Coupon1_Price VARCHAR(50)   
    		)as XMLInput
    	UNION
    	SELECT   
    		XMLInput.Coupon2,
    		XMLInput.Coupon2_PriceType,     
    		XMLInput.Coupon2_Price
    		FROM OPENXML(@idoc,'/Root/Array-DataNode_1/DataNode_1',2)
    		WITH
    		(  
    			Coupon2 VARCHAR(50),
    			Coupon2_PriceType VARCHAR(50),
    			Coupon2_Price VARCHAR(50)   
    		)as XMLInput
    
    

    where @coupons is you input Xml

     

    • Marked as answer by Sudhesh.G Friday, February 18, 2011 9:12 AM
    Thursday, February 17, 2011 9:45 PM

All replies

  • please guys tell the solution because its very urgent task to do

    Sudhesh. G
    http://gurucoders.blogspot.com
    Thursday, February 17, 2011 3:13 PM
  • Sudhesh.G - sorry this will be an incomplete answer and may not be the direction you want to go, but I've just finished working my first venture into Linq, here's msdn for more features, info and how'to's

    http://msdn.microsoft.com/en-us/library/bb387098(v=VS.90).aspx

    consider the following that loads an xml file, advances to the Party node element and then gets the first "Person" element. For your application someone may have a better solution that matches element/value into your local variables for you to then write to SQL.

        string XMLTemplate = XMLTemplatePath + XMLTemplateFile;
        XDocument rootLinq = XDocument.Load(XMLTemplate);
        var eParty = (from elm in rootLinq.Elements("TXLife").Elements("TXLifeRequest").Elements("OLifE").Elements()
                where elm.Name == "Party" 
                && elm.Attribute("id").Value.Substring(0, 13) == "InsuredParty_"
        select elm).Single();
        var ePerson = eParty.Element("Person");
    

     similarly you can use a foreach (omit .Single()) and place each NV pair into local variables as a class and then pass that to SQL, sorry again but perhaps you'll be a bit closer to what you want


    JeffP...
    Thursday, February 17, 2011 8:14 PM
  • If using OPENXML solution:

    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @coupons
    
    INSERT INTO TouchPointCoupon(coupon_category, price_type, price)   
    	SELECT   
    		XMLInput.Coupon1,
    		XMLInput.Coupon1_PriceType,     
    		XMLInput.Coupon1_Price
    		FROM OPENXML(@idoc,'/Root/Array-DataNode_1/DataNode_1',2)
    		WITH
    		(  
    			Coupon1 VARCHAR(50),
    			Coupon1_PriceType VARCHAR(50),
    			Coupon1_Price VARCHAR(50)   
    		)as XMLInput
    	UNION
    	SELECT   
    		XMLInput.Coupon2,
    		XMLInput.Coupon2_PriceType,     
    		XMLInput.Coupon2_Price
    		FROM OPENXML(@idoc,'/Root/Array-DataNode_1/DataNode_1',2)
    		WITH
    		(  
    			Coupon2 VARCHAR(50),
    			Coupon2_PriceType VARCHAR(50),
    			Coupon2_Price VARCHAR(50)   
    		)as XMLInput
    
    

    where @coupons is you input Xml

     

    • Marked as answer by Sudhesh.G Friday, February 18, 2011 9:12 AM
    Thursday, February 17, 2011 9:45 PM
  • thank u greg for the reply. can't do this in dynamically using one select statement
    Sudhesh. G
    http://gurucoders.blogspot.com
    Friday, February 18, 2011 5:10 AM