locked
xml file RRS feed

  • Question

  • Hi all.. I need your help.. I got an issue about xml file. I want to convert this below XML file to cursor. I read from help file and I got that I can use xmltocursor() function and then I use this syntax to convert the xml file to cursor

    Xmltocursor("C:\xmltocursor\1.xml", "tbl",512)
    SELECT * FROM tbl
    But when I run it, I just got 3 fields : customerinformation,orderinformation & side.
    1.customerinformation contains firstname
    2.orderinformation contains OrderCode & OrderDate
    3.side contains Side Quantity, Side, Diameter, ItemPrintOut, Sphere, Cylinder, Axis, Addition, Side Quantity, Side, Diameter, ItemPrintOut, Sphere, Cylinder, Axis, Addition

    What I want is to divide the xml file into this fields:

    1. FirstName
    2. OrderCode
    3. OrderDate
    4. Side Quantity
    5. Side
    6. Diameter
    7. ItemPrintOut
    8. Sphere
    9. Cylinder
    10. Axis
    11. Addition
    12. Side Quantity
    13. Side
    14. Diameter
    15. ItemPrintOut
    16. Sphere
    17. Cylinder
    18. Axis
    19. Addition

    Below is my XML file. So what should I do? Thanks a lot..Really hope your help..



    <?xml version="1.0" encoding="UTF-8" ?>
    - <OpticalOrders SchemaVersion="1.0" CompanyName="AA">
    - <OpticalOrder>
    - <CustomerInformation>
    <FirstName>Angel</FirstName>
    </CustomerInformation>
    - <OrderInformation>
    <OrderCode>006-622-940-BEO</OrderCode>
    <OrderDate>2008-05-03</OrderDate>
    <Remark />
    </OrderInformation>
    - <Side Quantity="1" Side="Right">
    <Diameter>75</Diameter>
    <ItemPrintOut>PRO15-DUR-HMC</ItemPrintOut>
    <Sphere>+ 2,25</Sphere>
    <Cylinder>+ 0,25</Cylinder>
    <Axis>160</Axis>
    <Addition>2,50</Addition>
    </Side>
    - <Side Quantity="1" Side="Left">
    <Diameter>75</Diameter>
    <ItemPrintOut>PRO15-DUR-HMC</ItemPrintOut>
    <Sphere>+ 3,00</Sphere>
    <Cylinder>+ 0,00</Cylinder>
    <Axis>000</Axis>
    <Addition>2,50</Addition>
    </Side>
    </OpticalOrder>

    Monday, May 26, 2008 2:18 AM

Answers

  •  cephie wrote:
    I read from help file and I got that I can use xmltocursor() function and then I use this syntax to convert the xml file to cursor

    Xmltocursor("C:\xmltocursor\1.xml", "tbl",512)
    SELECT * FROM tbl
    But when I run it, I just got 3 fields : customerinformation,orderinformation & side.

     

    XMLTOCURSOR() only handles "flat" XML files. Since your XML file is hierarchical, you are probably going to need to create an XSD schema for it and use an XMLadapter to create your cursor. I have not used the XMLadapter very much, but I think that it will handle this - you can get information on iot by looking up XMLadapter in the VFP help file.

     

    If the XMLadapter cannot be used to handle this, you will have to roll your own using the XML DOM.

     

    Monday, May 26, 2008 10:36 AM

All replies

  •  cephie wrote:
    I read from help file and I got that I can use xmltocursor() function and then I use this syntax to convert the xml file to cursor

    Xmltocursor("C:\xmltocursor\1.xml", "tbl",512)
    SELECT * FROM tbl
    But when I run it, I just got 3 fields : customerinformation,orderinformation & side.

     

    XMLTOCURSOR() only handles "flat" XML files. Since your XML file is hierarchical, you are probably going to need to create an XSD schema for it and use an XMLadapter to create your cursor. I have not used the XMLadapter very much, but I think that it will handle this - you can get information on iot by looking up XMLadapter in the VFP help file.

     

    If the XMLadapter cannot be used to handle this, you will have to roll your own using the XML DOM.

     

    Monday, May 26, 2008 10:36 AM
  • Thanks a lot especially for MarciaAkins, to reply both in here and in foxite. Let me also post both in foxcite and here, incase somebody here also can help me to figure out my issue. I got this source code from a good guy in foxite. but I want to make some modifications.

    What I mean is I need to know:
    1. How to count the total customer inside? Because i need to make a limitation for "looping"
    2. How to know if the customer just buying 1 lens (meaning just the left side or right side)? meaning that if the customer just buy 1 lens, I must save it just 1 row only (for example if just left side, I just need to save the left side only, then check the next customer. If they buy right and left, i need to save it into 2 rows (first is right and the second is for left,). Below I also give the example of XML file if customer buy just 1 lens or two lenses. What I have done is like this


    create cursor tbl (FirstName Char (20),;
    OrderCode Char(20),;
    OrderDate Date,;
    Side_Quantity C(5),;
    Side Char(10),;
    Diameter C(6),;
    ItemPrintOut Char(20),;
    Sphere C(6),;
    Cylinder C(6),;
    Axis C(3),;
    Addition C(6) )

    SET POINT TO ','
    SET DATE TO YMD
    SET STRICTDATE TO 1

    Select tbl
    Local Optics As Object
    Scatter Name Optics
    lcXML=Filetostr('C:\OSS\xmltocursor\1.xml')
    i=0
    *I need to give limitation for this ***FOR j=1 TO 100***
    FOR j=1 TO 100
    With Optics
    *if just left or right, no need to do this part ***IF MOD(j,2)=0***
    IF MOD(j,2)=0
    .FirstName=Strextract(lcXML,'<firstname>','</firstname>',m.j-1,1)
    .OrderDate=Strextract(lcXML,'<orderdate>','</orderdate>',m.j-1,1)
    .OrderDate=Ctod(.OrderDate)
    .OrderCode=Strextract(lcXML,'<ordercode>','</ordercode>',m.j-1,1)
    *For i = 1 To 2
    .Side_Quantity = Strextract(lcXML,'<side quantity=',' ',M.j,1)
    .Side = Strextract(lcXML,'side=','>',M.j,1)
    .Diameter= Strextract(lcXML,'<diameter>','</diameter>',M.j,1)
    .ItemPrintOut=Strextract(lcXML,'<ItemPrintOut>','</ItemPrintOut>',m.j,1)
    .Sphere=Strextract(lcXML,'<Sphere>','</Sphere>',m.j,1)
    .Cylinder=Strextract(lcXML,'<Cylinder>','</Cylinder>',m.j,1)
    .Axis=Strextract(lcXML,'<Axis>','</Axis>',m.j,1)
    .Addition=Strextract(lcXML,'<Addition>','</Addition>',m.j,1)
    Insert Into tbl From Name Optics
    *MESSAGEBOX(m.j)
    ENDIF
    IF MOD(j,2)=1
    .FirstName=Strextract(lcXML,'<firstname>','</firstname>',m.j,1)
    .OrderDate=Strextract(lcXML,'<orderdate>','</orderdate>',m.j,1)
    .OrderDate=Ctod(.OrderDate)
    .OrderCode=Strextract(lcXML,'<ordercode>','</ordercode>',m.j,1)
    *For i = 1 To 2
    .Side_Quantity = Strextract(lcXML,'<side quantity=',' ',M.j,1)
    .Side = Strextract(lcXML,'side=','>',M.j,1)
    .Diameter= Strextract(lcXML,'<diameter>','</diameter>',M.j,1)
    .ItemPrintOut=Strextract(lcXML,'<ItemPrintOut>','</ItemPrintOut>',m.j,1)
    .Sphere=Strextract(lcXML,'<Sphere>','</Sphere>',m.j,1)
    .Cylinder=Strextract(lcXML,'<Cylinder>','</Cylinder>',m.j,1)
    .Axis=Strextract(lcXML,'<Axis>','</Axis>',m.j,1)
    .Addition=Strextract(lcXML,'<Addition>','</Addition>',m.j,1)
    Insert Into tbl From Name Optics
    ENDIF


    ENDWITH
    next



    SELECT * FROM tbl

    *******************************************XML FILE******************************************************
    <?xml version="1.0" encoding="UTF-8" ?>
    <OpticalOrders SchemaVersion="1.0" CompanyName="BlueEyes">
    <OpticalOrder>
    <CustomerInformation>
    <FirstName>940 LANGI</FirstName>
    </CustomerInformation>
    <OrderInformation>
    <OrderCode>006-622-940-BEO</OrderCode>
    <OrderDate>2008-05-03</OrderDate>
    <Remark></Remark>
    </OrderInformation>
    <Side Quantity="1" Side="Right">
    <Diameter>75</Diameter>
    <ItemPrintOut>PRO15-DUR-HMC</ItemPrintOut>
    <Sphere>+ 2,25</Sphere>
    <Cylinder>+ 0,25</Cylinder>
    <Axis>160</Axis>
    <Addition>2,50</Addition>
    </Side>
    <Side Quantity="1" Side="Left">
    <Diameter>75</Diameter>
    <ItemPrintOut>PRO15-DUR-HMC</ItemPrintOut>
    <Sphere>+ 3,00</Sphere>
    <Cylinder>+ 0,00</Cylinder>
    <Axis>000</Axis>
    <Addition>2,50</Addition>
    </Side>
    </OpticalOrder>
    ***just 1 side=> right side only****
    <OpticalOrder>
    <CustomerInformation>
    <FirstName>785 ALVES</FirstName>
    </CustomerInformation>
    <OrderInformation>
    <OrderCode>069-386-785-REC</OrderCode>
    <OrderDate>2008-05-03</OrderDate>
    <Remark></Remark>
    </OrderInformation>
    <Side Quantity="1" Side="Right">
    <Diameter>70</Diameter>
    <ItemPrintOut>TOP13-DUR-HMC</ItemPrintOut>
    <Sphere>+ 0,25</Sphere>
    <Cylinder>+ 0,50</Cylinder>
    <Axis>135</Axis>
    <Addition>2,25</Addition>
    </Side>
    </OpticalOrder>
    </OpticalOrders>





    Tuesday, May 27, 2008 8:44 AM