XML Formatting Using SQL

  • Can someone recommend how to create the following XML file with data that I already have in my database...?

    There will be many transaction elements...eg <transaction id-type="VENDOR-CUSTOMER" id="76" seq="2"></transaction>

    Here's the format...

    <?xml version="1.0" encoding="ISO-8859-1"?>

    <vip-customer-load created-date-time="2013-06-26 14:55" origin="ISP">

    <data>

    <transaction id-type="VENDOR-CUSTOMER" id="4823798" seq="4">

    <customer service="ISP" country="US" action="ADD">

    <vendor-account-id>SOMEDATA</vendor-account-id>

    <customer-status>ACTIVE</customer-status>

    <credit-decision></credit-decision>

    <teamid>708</teamid>

    <name>

    <first>JOE </first>

    <middle></middle>

    <last>BLOW</last>

    <suffix></suffix>

    </name>

    <language>en</language>

    <currency>USD</currency>

    <ssn></ssn>

    <startdate>2013-03-31</startdate>

    <address>

    <street>123 Main St</street>

    <apt></apt>

    <double-locality></double-locality>

    <city>HANALEI</city>

    <state>HI</state>

    <country>US</country>

    <postal-code>84098</postal-code>

    </address>

    <phone>8009183278</phone>

    <alternate-phone></alternate-phone>

    <residential>Y</residential>

    <entered-by>ONLINE</entered-by>

    <loa-date>2013-03-31</loa-date>

    <rate-plan>09</rate-plan>

    <enrollment-method></enrollment-method>

    <physical-address></physical-address>

    <rep-verified></rep-verified>

    <account-number></account-number>

    <business-name></business-name>

    <reason-code></reason-code>

    <is-rep></is-rep>

    </customer>

    </transaction>

    </data>

    <control>

    <customer service="ISP" country="US">

    <transaction-count>4</transaction-count>

    <add-count>1</add-count>

    <change-count>3</change-count>

    <delete-count>0</delete-count>

    <vendor-system-total-count>9082</vendor-system-total-count>

    <vendor-system-active-count>150</vendor-system-active-count>

    <vendor-system-incomplete-count>0</vendor-system-incomplete-count>

    </customer>

    </control>

    </vip-customer-load>

  • It's a lot of work so I can't do it for you, but here's some basic examples:

    [Repost from: http://www.sqlservercentral.com/Forums/FindPost1375776.aspx]

    DECLARE

    @x XML

    ,@s VARCHAR(MAX)

    DECLARE @TempTable TABLE

    (

    ID INT IDENTITY(1,1) NOT NULL

    ,Part VARCHAR(10)

    ,Color VARCHAR(10)

    ,Size VARCHAR(10)

    )

    INSERT INTO @TempTable

    SELECT '123','blue','small'

    UNION ALL

    SELECT '124','black','medium'

    UNION ALL

    SELECT '125','red','large'

    /* Note: The part number tag formatted as '<123>' is an invalid XML tag */

    /* and SQL will not render. This will create a pseudo-XML string using the */

    /* integer part number as tag (non-XML compliant) */

    SET @x =

    (

    SELECT

    CAST(Part AS VARCHAR(50)) AS 'StartPart'

    ,(SELECT

    temp.Color AS 'Color'

    ,temp.Size AS 'Size'

    ,CAST(Part AS VARCHAR(50)) AS 'EndPart'

    FOR XML PATH(''),TYPE

    )

    FROM

    @TempTable AS temp

    FOR XML PATH(''), ROOT('PartsList')

    )

    SET @S = CONVERT(VARCHAR(MAX), @x)

    SET @S =

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(@s,'</StartPart>','>')

    ,'<StartPart>','<')

    ,'</EndPart>','>')

    ,'<EndPart>','</')

    SELECT @S AS String_Result

    <PartsList>

    <123>

    <Color>blue</Color>

    <Size>small</Size>

    </123>

    <124>

    <Color>black</Color>

    <Size>medium</Size>

    </124>

    <125>

    <Color>red</Color>

    <Size>large</Size>

    </125>

    </PartsList>

    Properly formatted XML examples

    /* Adds ascending character value to part number to create */

    /* valid XML tag and sets the part number as its own parent tag */

    /* with 'Color' and 'Size' as child tags. */

    SET @x =

    (

    SELECT

    CAST(CHAR(temp.ID+64) AS VARCHAR(3))

    +CAST(Part AS VARCHAR(50)) AS 'StartPart'

    ,(SELECT

    temp.Color AS 'Color'

    ,temp.Size AS 'Size'

    ,CAST(CHAR(temp.ID+64) AS VARCHAR(3))

    +CAST(Part AS VARCHAR(50)) AS 'EndPart'

    FOR XML PATH(''),TYPE

    )

    FROM

    @TempTable AS temp

    FOR XML PATH(''), ROOT('PartsList')

    )

    SET @S = CONVERT(VARCHAR(MAX), @x)

    SET @S =

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(@s,'</StartPart>','>')

    ,'<StartPart>','<')

    ,'</EndPart>','>')

    ,'<EndPart>','</')

    SET @x = CONVERT(XML, @S)

    SELECT @x AS XML_Result

    <PartsList>

    <A123>

    <Color>blue</Color>

    <Size>small</Size>

    </A123>

    <B124>

    <Color>black</Color>

    <Size>medium</Size>

    </B124>

    <C125>

    <Color>red</Color>

    <Size>large</Size>

    </C125>

    </PartsList>

    /* Sets part number as single un-nested 'Part' tag which is */

    /* followed sequentially by 'Color' and 'Size' for each part number */

    SET @x =

    (

    SELECT

    CAST(Part AS VARCHAR(50)) AS 'Part'

    ,(SELECT

    temp.Color AS 'Color'

    ,temp.Size AS 'Size'

    FOR XML PATH(''),TYPE

    )

    FROM

    @TempTable AS temp

    FOR XML PATH(''), ROOT('PartsList')

    )

    SELECT

    @x AS XML_Result

    <PartsList>

    <Part>123</Part>

    <Color>blue</Color>

    <Size>small</Size>

    <Part>124</Part>

    <Color>black</Color>

    <Size>medium</Size>

    <Part>125</Part>

    <Color>red</Color>

    <Size>large</Size>

    </PartsList>

    /* Sets parent 'Part' tag with 'Color' and 'Size' as child tags */

    SET @x =

    (

    SELECT

    (SELECT ISNULL (CAST(Part AS VARCHAR (50)),'') AS 'Number'

    FOR XML PATH(''), TYPE)

    ,(SELECT ISNULL (CAST(Color AS VARCHAR (50)),'') AS 'Color'

    FOR XML PATH(''), TYPE)

    ,(SELECT ISNULL (CAST(Size AS VARCHAR (50)),'') AS 'Size'

    FOR XML PATH('') ,TYPE)

    FROM @TempTable

    FOR XML PATH('Part'), ROOT('PartsList')

    )

    SELECT @x AS XML_Result

    <PartsList>

    <Part>

    <Number>123</Number>

    <Color>blue</Color>

    <Size>small</Size>

    </Part>

    <Part>

    <Number>124</Number>

    <Color>black</Color>

    <Size>medium</Size>

    </Part>

    <Part>

    <Number>125</Number>

    <Color>red</Color>

    <Size>large</Size>

    </Part>

    </PartsList>

    /* Sets parent 'Part' tag with a property value 'number=[part number]' */

    /* with 'Color' and 'Size' as child tags */

    SET @x =

    (

    SELECT

    (

    SELECT

    Part AS 'Part/@number'

    ,Color AS 'Part/Color'

    ,Size AS 'Part/Size'

    FOR XML PATH(''), TYPE

    )

    FROM @TempTable

    FOR XML PATH(''), ROOT('PartsList')

    )

    SELECT @x AS XML_Result

    <PartsList>

    <Part number="123">

    <Color>blue</Color>

    <Size>small</Size>

    </Part>

    <Part number="124">

    <Color>black</Color>

    <Size>medium</Size>

    </Part>

    <Part number="125">

    <Color>red</Color>

    <Size>large</Size>

    </Part>

    </PartsList>

  • OK, found another example.

    SET NOCOUNT ON

    DECLARE

    @strSQL NVARCHAR(MAX)

    SET @strSQL = '' -- required or the concatenation will return null

    ;WITH cteStore (SalesPersonID,TerritoryID) -- create some sample data

    AS

    (

    SELECT 1,2 UNION ALL

    SELECT 2,2 UNION ALL

    SELECT 3,2 UNION ALL

    SELECT 4,3 UNION ALL

    SELECT 5,3 UNION ALL

    SELECT 6,2 UNION ALL

    SELECT 7,2

    ),

    cteSalesPerson (SalesPersonID,SalesPersonName,ModifiedDate)

    AS

    (

    SELECT 1,'George Washington','2013-03-31' UNION ALL

    SELECT 2,'John Adams','2013-02-28' UNION ALL

    SELECT 3,'Thomas Jefferson','2013-02-15' UNION ALL

    SELECT 4,'James Madison','2013-02-01' UNION ALL

    SELECT 5,'James Monroe','2013-01-31' UNION ALL

    SELECT 6,'John Q Adams','2013-01-22' UNION ALL

    SELECT 1,'George Washington','2013-03-21' UNION ALL

    SELECT 7,'Andew Jackson','2013-01-13' UNION ALL

    SELECT 3,'Thomas Jefferson','2013-02-05'

    )

    -- create a pseudo-XML string

    SELECT

    @strSQL = @strSQL + CAST(r.strXML AS NVARCHAR(MAX))

    FROM

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION BY p1.SalesPersonID ORDER BY p1.SalesPersonID) AS rn

    ,(SELECT

    (SELECT

    s.SalesPersonID

    ,s.TerritoryID

    FROM

    cteStore AS s

    WHERE

    s.SalesPersonID = t.N

    FOR XML PATH(''), TYPE)

    ,(SELECT

    (SELECT

    p.SalesPersonName AS 'Name'

    ,p.ModifiedDate

    FROM

    cteSalesPerson AS p

    WHERE

    p.SalesPersonID = t.N

    ORDER BY

    p.SalesPersonID

    FOR XML PATH('Sale'), TYPE)

    FOR XML PATH('Sales'), TYPE)

    FOR XML PATH(''), TYPE)

    AS strXML

    FROM

    cteSalesPerson p1

    INNER JOIN

    dbo.Tally t

    ON t.N = p1.SalesPersonID

    ) r

    WHERE

    rn = 1

    ORDER BY

    rn

    -- convert the string into XML

    SELECT CONVERT(XML,@strSQL) AS XML_Result

  • Thanks Steven! I've been able to get almost everything formatted the way I want by using some of your examples.

    Now I just need to append a separate <control> element to the bottom of the results of this query:

    DECLARE @main TABLE ( [created-date-time] DATETIME, origin VARCHAR (5) ) -- need to fix date. remove tab char

    INSERT INTO @main VALUES ( GETDATE(), 'IKA' )

    SELECT [created-date-time] AS [@created-date-time], origin AS [@origin],

    (SELECT TOP 2 id_type AS [@id-type], id AS [@id], seq AS [@seq],

    (SELECT [SERVICE] AS [@service], country AS [@country],[action] AS [@action],

    (SELECT UPPER([Username])

    FOR XML PATH('vendor-account-id'), TYPE),

    (SELECT [status] AS [customer-status]

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'credit-decision'

    FOR XML PATH(''), TYPE),

    (SELECT [TeamID] AS 'team-id'

    FOR XML PATH(''), TYPE),

    (SELECT [FirstName] AS 'first', [MiddleName] AS 'middle', [LastName] AS 'last', [suffix]

    FOR XML PATH('name'), TYPE),

    (SELECT 'en' AS 'language'

    FOR XML PATH(''), TYPE),

    (SELECT currency

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'ssn'

    FOR XML PATH(''), TYPE),

    (SELECT [loa_date] AS [startdate]-- need to fix date. remove tab char

    FOR XML PATH(''), TYPE),

    (SELECT [street], '' AS [apt], '' AS [double-locality], [city], [state], [country], rtrim([PostalCode]) AS 'postal-code'

    FOR XML PATH('adress'), TYPE),

    (SELECT phone

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'alternate-phone'

    FOR XML PATH(''), TYPE),

    (SELECT 'Y' AS 'residential'

    FOR XML PATH(''), TYPE),

    (SELECT 'ONLINE' AS 'entered-by'

    FOR XML PATH(''), TYPE),

    (SELECT [loa_date]-- need to fix date. remove tab char

    FOR XML PATH(''), TYPE),

    (SELECT [rate_plan] AS 'rate-plan' -- might need to be 2-digit eg... 09 not 9

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'enrollment-method'

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'rep-verified'

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'account-number'

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'business-name'

    FOR XML PATH(''), TYPE),

    (SELECT [reason_code] AS [reason-code]

    FOR XML PATH(''), TYPE),

    (SELECT '' AS 'is-rep'

    FOR XML PATH(''), TYPE)

    FOR XML PATH('customer'), TYPE

    )

    FROM #acnUpdates

    FOR XML PATH ('transaction'), type--ROOT ('vip-customer-load')

    )

    FOR XML PATH('data'), TYPE

    )

    FROM @main m

    FOR XML PATH('vip-customer-load'), TYPE

    Gives me these results:

    <vip-customer-load created-DATE-TIME="2013-07-11T23:14:28.053" origin="IKA">

    <data>

    <transaction id-TYPE="VENDOR-CUSTOMER" id="adsfas722" seq="1">

    <customer service="ISP" country="CA" action="INACTIVE">

    <vendor-account-id>SQLTEST_Gdfadsfsd</vendor-account-id>

    <customer-status>CHANGE</customer-status>

    <credit-decision />

    <team-id>asdfsad7104</team-id>

    <name>

    <first>dsfadsf</first>

    <middle />

    <last>adsfasd</last>

    <suffix />

    </name>

    <language>en</language>

    <currency>CAD</currency>

    <ssn />

    <startdate>2010-05-19T00:00:00</startdate>

    <adress>

    <street>1762 Du Lac </street>

    <apt />

    <double-locality />

    <city>dsafasd</city>

    <state>QC</state>

    <country>CA</country>

    <postal-code>fdsa</postal-code>

    </adress>

    <phone>dsafasd29434/phone>

    <alternate-phone />

    <residential>Y</residential>

    <entered-by>ONLINE</entered-by>

    <loa-date>2010-05-19T00:00:00</loa-date>

    <rate-plan>9</rate-plan>

    <enrollment-method />

    <rep-verified />

    <account-number />

    <business-name />

    <reason-code>70,71</reason-code>

    <is-rep />

    </customer>

    </transaction>

    <transaction id-TYPE="VENDOR-CUSTOMER" id="27dfs" seq="2">

    <customer service="ISP" country="CA" action="INACTIVE">

    <vendor-account-id>SQLTdfasfasd</vendor-account-id>

    <customer-status>CHANGE</customer-status>

    <credit-decision />

    <team-id>fadsfasd</team-id>

    <name>

    <first>fadsdf</first>

    <middle />

    <last>adsfadf</last>

    <suffix />

    </name>

    <language>en</language>

    <currency>CAD</currency>

    <ssn />

    <startdate>2010-05-19T00:00:00</startdate>

    <adress>

    <street>176dfasf</street>

    <apt />

    <double-locality />

    <city>Clarenfdasfc/city>

    <state>QC</state>

    <country>CA</country>

    <postal-code>j0j1fdasb0</postal-code>

    </adress>

    <phone>4502943fasdfsafa440</phone>

    <alternate-phone />

    <residential>Y</residential>

    <entered-by>ONLINE</entered-by>

    <loa-date>2010-05-19T00:00:00</loa-date>

    <rate-plan>9</rate-plan>

    <enrollment-method />

    <rep-verified />

    <account-number />

    <business-name />

    <reason-code>70,71</reason-code>

    <is-rep />

    </customer>

    </transaction>

    </data>

    </vip-customer-load>

    Now I need to append the following element to the above results...

    <control>

    <customer service="ISP" country="CA">

    <transaction-count>4</transaction-count>

    <add-count>1</add-count>

    <change-count>3</change-count>

    <delete-count>0</delete-count>

    <vendor-system-total-count>9082</vendor-system-total-count>

    <vendor-system-active-count>150</vendor-system-active-count>

    <vendor-system-incomplete-count>0</vendor-system-incomplete-count>

    </customer>

    </control>

    Thanks...!

  • I'm also having a hard time appending this to the top of the file:

    <?xml version="1.0" encoding="ISO-8859-1"?>

    Any suggestions...?

  • brickpack (7/12/2013)


    I'm also having a hard time appending this to the top of the file:

    <?xml version="1.0" encoding="ISO-8859-1"?>

    Any suggestions...?

    I usually hard-code any header info and append it manually to the XML file. It won't usually render it properly in the SSMS window. You can use a browser or I use Programmer's Notepad++ (a free dowload) which handles every format possible. Once you have confirmed the XML is valid, and appended any header info and declarations, then it's usually converted back to NVARCHAR anyway prior to being posted via an HTTP client or whatever.

     

  • I actually need to automatically generate this file on a daily basis and upload it to an FTP site. I'm hoping that I can accomplish this whole process using SSIS. Do I need to get C# (or other) involved in this or can I create the whole XML document in T-SQL...?

  • brickpack (7/12/2013)


    I actually need to automatically generate this file on a daily basis and upload it to an FTP site. I'm hoping that I can accomplish this whole process using SSIS. Do I need to get C# (or other) involved in this or can I create the whole XML document in T-SQL...?

    The code below posts to a file handler page, but it should be just as easy to post to an FTP location. This is the main procedure I use to make literally hundreds of posts every day to to a major vendor.

    /*

    Must run this code on the server to activate the API

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'Ole Automation Procedures', 1

    RECONFIGURE

    Then, give EXECUTE permission to the following system stored procedures

    sp_OACreate

    sp_OAMethod

    sp_OAGetProperty

    sp_OADestroy

    */

    CREATE PROCEDURE dbo.HTTP_POST_ByGUID

    @uMember_id UNIQUEIDENTIFIER

    ,@sDescription VARCHAR(255)

    ,@sRequestURL VARCHAR(1000)

    ,@sXML VARCHAR(4000)

    AS

    BEGIN

    /*

    EXEC dbo.HTTP_POST_ByGUID

    '7C4551F0-4A4C-4FD9-B996-000001815FEF'

    ,'Title for the transaction here'

    ,'https://www.someURL/request.ashx'

    , '[....raw varchar XML string here...]'

    */

    SET NOCOUNT ON

    DECLARE

    @iPointer INT

    ,@sResponseText VARCHAR(4000)

    ,@iStatus INT

    ,@sStatusText VARCHAR(4000)

    ,@iCheckSource INT

    ,@sSource VARCHAR(4000)

    ,@xmlPostValue VARCHAR(4000)

    /* Build the XML string */

    SET @sSource =

    '<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:cp="http://www.cpscreen.com/schemas" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'

    +'<soapenv:Body>'

    +'<BackgroundReports xmlns="http://www.cpscreen.com/schemas">'

    +@sXML

    +'</BackgroundReports></soapenv:Body></soapenv:Envelope>'

    SET @xmlPostValue = @sSource

    SET @xmlPostValue = 'xml='+@xmlPostValue

    SET @iCheckSource = LEN(@xmlPostValue)

    IF @iCheckSource > 4000

    BEGIN

    SELECT

    'XML source post must be less than 4000 chars (='+CAST(@iCheckSource AS VARCHAR(10))+')' AS TooManyCharacters

    ,@xmlPostValue AS [XMLPostValue]

    ,@sDescription AS [Description]

    RETURN

    END

    EXEC sp_OACreate

    'MSXML2.ServerXMLHTTP'

    ,@iPointer OUTPUT

    --Check to see if errors where created.

    EXEC sp_OAGetErrorInfo

    @iPointer

    ,@sSource OUT

    ,@sDescription OUT

    IF @sSource IS NOT NULL

    BEGIN

    SELECT

    'Error While Creating HTTP Object' AS SourceError

    ,@sSource AS [Source]

    ,@sDescription AS [Description]

    RETURN

    END

    -- Open a connection to the URL.

    EXEC sp_OAMethod

    @iPointer

    ,'OPEN'

    ,NULL

    ,'POST'

    ,@sRequestURL

    EXEC sp_OAGetErrorInfo

    @iPointer

    ,@sSource OUT

    ,@sDescription OUT

    IF @sSource IS NOT NULL

    BEGIN

    SELECT

    'Error While opening connection' AS ConnectionError

    ,@sSource AS Source

    ,@sDescription AS [Description]

    RETURN

    END

    -- Send the request.

    EXEC sp_OAMethod

    @iPointer

    ,'send'

    ,NULL

    ,@xmlPostValue

    EXEC sp_OAGetErrorInfo

    @iPointer

    ,@sSource OUT

    ,@sDescription OUT

    IF @sSource IS NOT NULL

    BEGIN

    SELECT

    'Error While sending data' AS SendError

    ,@sSource AS Source

    ,@sDescription AS [Description]

    RETURN

    END

    -- Send the request.

    EXEC sp_OAMethod

    @iPointer

    ,'responseText'

    ,@sResponseText OUTPUT

    EXEC sp_OAMethod

    @iPointer

    ,'Status'

    ,@iStatus OUTPUT

    EXEC sp_OAMethod

    @iPointer

    ,'StatusText'

    ,@sStatusText OUTPUT

    EXEC sp_OADestroy

    @iPointer

    WAITFOR DELAY '00:00:03'

    SELECT

    @uMember_id AS uMember_id

    ,@iStatus AS StatusCode

    ,@sStatusText AS StatusText

    ,@sResponseText AS ResponseText

    END

     

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

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