Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Creating xml from SQL query

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 75
    Comment on it

    This blog will show you how to create an xml from sql query having sql data. Write down the followng query to generate xml:-

    WITH XMLNAMESPACES ('http://www.dossia.org/v2.0/xml/phr' as phr)
    Select OrderResultID,OrderID ,PatientID ,SpecimenNumber ,AccountNumber ,AccountPhoneNumber 
    ,PatientLastName ,PatientFirstName ,PatientMiddleInitial ,PatientSSN ,PatientPhone 
    ,PatientDOB ,PatientAgeYMD ,PatientSex from OrderResults
    where PatientID = 123456
    FOR XML RAW ('phr:Encounter'), ELEMENTS
    

    This will generate an xml like following:-

    <phr:Encounter xmlns:phr="http://www.dossia.org/v2.0/xml/phr">
    <OrderResultID>29058</OrderResultID>
    <OrderID>34584</OrderID>
    <PatientID>24703</PatientID>
    <SpecimenNumber>17961202890</SpecimenNumber>
    <AccountNumber>09359830</AccountNumber>
    <PatientLastName>Burke</PatientLastName>
    <PatientFirstName>Michelle </PatientFirstName>
    <PatientMiddleInitial></PatientMiddleInitial>
    <PatientSSN></PatientSSN>
    <PatientPhone>239-425-6565</PatientPhone>
    <PatientDOB>19840911</PatientDOB>
    <PatientAgeYMD>19840911</PatientAgeYMD>
    <PatientSex>F</PatientSex>
    </phr:Encounter>
    

    we just need to write the namespace which will be used in xml like:-

    WITH XMLNAMESPACES ('http://www.dossia.org/v2.0/xml/phr' as phr)
    

    and at the end of query need to give FOR XML RAW ('phr:Encounter'), ELEMENTS for creating xml.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: