Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Formatting Using SQL Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 5:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:41 PM
Points: 77, Visits: 572
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>

Post #1472823
Posted Thursday, July 11, 2013 5:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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>

Post #1472826
Posted Thursday, July 11, 2013 5:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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



Post #1472829
Posted Friday, July 12, 2013 12:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:41 PM
Points: 77, Visits: 572
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...!
Post #1472870
Posted Friday, July 12, 2013 10:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:41 PM
Points: 77, Visits: 572
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...?

Post #1473143
Posted Friday, July 12, 2013 11:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1473152
Posted Friday, July 12, 2013 11:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:41 PM
Points: 77, Visits: 572
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...?
Post #1473167
Posted Friday, July 12, 2013 12:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1473196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse