#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Sheffield
    Posts
    3
    Rep Power
    0

    Problem with SQL Server --- XML


    I need to produce this XML structure (only fragment shown)

    <Citizen citizenRef="1">
    <Title>Rear Admiral Sir</Title>
    <Forename>Horatio</Forename>
    <Initials>A B</Initials>
    <Surname>Nelson</Surname>
    <DisplayName>Rear Admiral Sir Horatio Nelson</DisplayName>
    <Gender>1</Gender>
    <DateOfBirth>1979-10-20</DateOfBirth>
    <Address>
    <Line>HMS Victory</Line>
    <Line>Victory Road</Line>
    <Line>Portsmouth</Line>
    <Line>Hampshire</Line>
    <PostCode>HA1 1AA</PostCode>
    </Address>
    <Password>test</Password>
    </Citizen>


    I'm using a SQL server source and using XML explicit. SQL server insists on putting the address fragment at the end :-


    <Citizen citizenRef="1">
    <Title>Rear Admiral Sir</Title>
    <Forename>Horatio</Forename>
    <Initials>A B</Initials>
    <Surname>Nelson</Surname>
    <DisplayName>Rear Admiral Sir Horatio Nelson</DisplayName>
    <Gender>1</Gender>
    <DateOfBirth>1979-10-20</DateOfBirth>
    <Password>test</Password>

    <Address>
    <Line>HMS Victory</Line>
    <Line>Victory Road</Line>
    <Line>Portsmouth</Line>
    <Line>Hampshire</Line>
    <PostCode>HA1 1AA</PostCode>
    </Address>
    </Citizen>[/COLOR]

    The client howeever needs the Address to follow DateofBirth.

    Can anyone advise as to how I can achieve this from SQL server (unfortunately the solution is needed yesterday)

    THanx

    Jeremy
    Last edited by Heavenlyarts; December 5th, 2003 at 06:22 PM.
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
    The application that "consumes" this xml is badly written.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Sheffield
    Posts
    3
    Rep Power
    0
    I know, had this argument with them with limited success.

    He based his critique (failure notification) purely on the fact that it did not validate thru XML spy.
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
    Validation is independent of the positioning!!!
    However you should investigate why it is not validated, does it start with the proper declaration?

    Anyway, if the client app uses dom and not string manipulation to parse XML, then it is independent of the positioning.
    I've done some work with cross-platform XML and XSLT and I have to say that M$ parsers are a bit permissive while Apache's are more picky about declarations and syntax (but that's the right way of doing things).
    Hope this helps!

IMN logo majestic logo threadwatch logo seochat tools logo