Post reply

XML

  • February 7, 2019 at 3:19 am

    #370535

    I need to create an XML file from an SQL Query.  The XML file has to look like the following:

    Creating a new account       

  •              

    <?xml version="1.0"?>
    <importdata>
     <area>001</area>
             <data>
                     <notify>admin@company.com</notify>
                     <account>
                             <field table="TELRCM" field="COMPANY">Test Industries</field>
                             <field table="TELRCM" field="POSTCODE">AA01 0AA</field>
                             <field table="TELRCM" field="TELEPHONE">01234 567890</field>
                             <field table="TELRCM" field="CUSTOMERSTATUS">Not called</field>
                             <field table="TELRCM" field="OWNER">New data</field>
                             <field table="RCMANL" field="ANAL01">25</field>
                     </account>
                     <contact>
                             <field table="CONT" field="CONTACT">Test 1</field>
                             <field table="CONT" field="EMAIL">test@a.com</field>
                     </contact>
                     <contact>
                             <field table="CONT" field="CONTACT">Test 2</field>
                             <field table="CONT" field="EMAIL">test@aa.com</field>
                     </contact>
                     <contact>
                             <field table="CONT" field="CONTACT">Test 3</field>
                             <field table="CONT" field="EMAIL">test@aaa.com</field>
                     </contact>
             </data>
             <data>
                     <notify>admin@company.com</notify>
                     <account>
                             <field table="TELRCM" field="COMPANY">Test2 Industries</field>
                             <field table="TELRCM" field="POSTCODE">AA01 0AA</field>
                             <field table="TELRCM" field="TELEPHONE">01234 567890</field>
                             <field table="TELRCM" field="CUSTOMERSTATUS">Not called</field>
                             <field table="TELRCM" field="OWNER">New data</field>
                             <field table="RCMANL" field="ANAL01">25</field>
                     </account>
                     <contact>
                             <field table="CONT" field="CONTACT">Test 11</field>
                             <field table="CONT" field="EMAIL">test@a.com</field>
                     </contact>
                     <contact>
                             <field table="CONT" field="CONTACT">Test 22</field>
                             <field table="CONT" field="EMAIL">test@aa.com</field>
                     </contact>
                     <contact>
                             <field table="CONT" field="CONTACT">Test 33</field>
                             <field table="CONT" field="EMAIL">test@aaa.com</field>
                     </contact>
             </data>
    </importdata>

    • I've never attempted to create an XML file before so have a bit of difficulty at the moment.  Any ideas on the best way to achieve this?

    ---temp SQL table with data---
    create table #testxmldata(
    Company varchar(50),
    postcode varchar(10),
    telephone varchar(15),
    customerstatus varchar(20),
    owner varchar(20),
    anal01 varchar(10),
    contact1 varchar(50),
    email1 varchar(50),
    contact2 varchar(50),
    email2 varchar(50),
    contact3 varchar(50),
    email3 varchar(50))

    insert into #testxmldata
    values    ('Test Industries','AA01 0AA','01234 567890','Not called','New data','25','Test 1','test@a.com','Test 2','test@aa.com','Test 3','test@aaa.com'),
            ('Test Industries2','AA01 0AA','01234 567890','Not called','New data','25','Test 11','test@a.com','Test 22','test@aa.com','Test 33','test@aaa.com')

  • February 7, 2019 at 5:42 am

    #2021362

    Try this

    SELECT '001' AS "area",
           (SELECT 'admin@company.com' AS "notify",
          (SELECT  'TELRCM' AS "field/@table", 'COMPANY' AS "field/@field", Company AS "field", null,
          'TELRCM' AS "field/@table", 'POSTCODE' AS "field/@field", postcode AS "field", null,
          'TELRCM' AS "field/@table", 'TELEPHONE' AS "field/@field", telephone AS "field", null,
          'TELRCM' AS "field/@table", 'CUSTOMERSTATUS' AS "field/@field", customerstatus AS "field", null,
          'TELRCM' AS "field/@table", 'OWNER' AS "field/@field", owner AS "field", null,
          'RCMANL' AS "field/@table", 'ANAL01' AS "field/@field", anal01 AS "field"
          FOR XML PATH('account'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact1 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email1 AS "field", null
          FOR XML PATH('contact'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact2 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email2 AS "field", null
          FOR XML PATH('contact'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact3 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email3 AS "field", null
          FOR XML PATH('contact'),TYPE)
      FROM #testxmldata
      FOR XML PATH('data'),TYPE)
    FOR XML PATH('importdata'),TYPE;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 7, 2019 at 6:44 am

    #2021372

    Fantastic!  Does exactly as needed.

    Thanks very much!

  • February 8, 2019 at 3:19 am

    #2021467

    If I needed to drop the notify from XML how would I achieve this?

  • February 8, 2019 at 5:23 am

    #2021479

    bicky1980 - Friday, February 8, 2019 3:19 AM

    If I needed to drop the notify from XML how would I achieve this?

    Remove the part of the SQL that states "'admin@company.com' AS "notify",".

    Your reply has the sound of that you don't understand how the answer that Mark has posted works. Do you? If not, it's important you take the time to understand it or ask questions about it. The person that needs to support the code above is you, not Mark, nor I, nor anyone else at the SSC community.

    If you don't understand how some parts of the SQL don't work, which part(s) is it? We can help you understand then. You could also take some time to have a read of the documentation on FOR XML, which might fill in some onf the blanks.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 8, 2019 at 5:27 am

    #2021481

    This should do it

    SELECT '001' AS "area",
           (SELECT --'admin@company.com' AS "notify",
          (SELECT  'TELRCM' AS "field/@table", 'COMPANY' AS "field/@field", Company AS "field", null,
          'TELRCM' AS "field/@table", 'POSTCODE' AS "field/@field", postcode AS "field", null,
          'TELRCM' AS "field/@table", 'TELEPHONE' AS "field/@field", telephone AS "field", null,
          'TELRCM' AS "field/@table", 'CUSTOMERSTATUS' AS "field/@field", customerstatus AS "field", null,
          'TELRCM' AS "field/@table", 'OWNER' AS "field/@field", owner AS "field", null,
          'RCMANL' AS "field/@table", 'ANAL01' AS "field/@field", anal01 AS "field"
          FOR XML PATH('account'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact1 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email1 AS "field", null
          FOR XML PATH('contact'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact2 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email2 AS "field", null
          FOR XML PATH('contact'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact3 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email3 AS "field", null
          FOR XML PATH('contact'),TYPE)
      FROM #testxmldata
      FOR XML PATH('data'),TYPE)
    FOR XML PATH('importdata'),TYPE;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 8, 2019 at 6:45 am

    #2021489

    Thom A - Friday, February 8, 2019 5:23 AM

    bicky1980 - Friday, February 8, 2019 3:19 AM

    If I needed to drop the notify from XML how would I achieve this?

    Remove the part of the SQL that states "'admin@company.com' AS "notify",".

    Your reply has the sound of that you don't understand how the answer that Mark has posted works. Do you? If not, it's important you take the time to understand it or ask questions about it. The person that needs to support the code above is you, not Mark, nor I, nor anyone else at the SSC community.

    If you don't understand how some parts of the SQL don't work, which part(s) is it? We can help you understand then. You could also take some time to have a read of the documentation on FOR XML, which might fill in some onf the blanks.

    I did try that Thorn to no avail.  And I have looked through the documentation, which I obviously don't really understand hence my question here.  But thanks for your advice...

    Edit: Looking back at what I did try I also removed the Select directly before this, so that is why it didn't work for me.

  • February 8, 2019 at 6:56 am

    #2021491

    Mark Cowne - Friday, February 8, 2019 5:27 AM

    This should do it

    SELECT '001' AS "area",
           (SELECT --'admin@company.com' AS "notify",
          (SELECT  'TELRCM' AS "field/@table", 'COMPANY' AS "field/@field", Company AS "field", null,
          'TELRCM' AS "field/@table", 'POSTCODE' AS "field/@field", postcode AS "field", null,
          'TELRCM' AS "field/@table", 'TELEPHONE' AS "field/@field", telephone AS "field", null,
          'TELRCM' AS "field/@table", 'CUSTOMERSTATUS' AS "field/@field", customerstatus AS "field", null,
          'TELRCM' AS "field/@table", 'OWNER' AS "field/@field", owner AS "field", null,
          'RCMANL' AS "field/@table", 'ANAL01' AS "field/@field", anal01 AS "field"
          FOR XML PATH('account'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact1 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email1 AS "field", null
          FOR XML PATH('contact'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact2 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email2 AS "field", null
          FOR XML PATH('contact'),TYPE),
          (SELECT  'CONT' AS "field/@table", 'CONTACT' AS "field/@field", contact3 AS "field", null,
          'CONT' AS "field/@table", 'EMAIL' AS "field/@field", email3 AS "field", null
          FOR XML PATH('contact'),TYPE)
      FROM #testxmldata
      FOR XML PATH('data'),TYPE)
    FOR XML PATH('importdata'),TYPE;

    Thanks for this again Mark.  Sorted the issue.  I will be looking at the query as advised by Thorn and making sure I understand how it works.  Thanks again

    • Viewing 8 posts - 1 through 7 (of 7 total)

    You must be logged in to reply to this topic. Login to reply