July 11, 2013 at 5:04 pm
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>
July 11, 2013 at 5:33 pm
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-2 = CONVERT(VARCHAR(MAX), @x)
SET @s-2 =
REPLACE(
REPLACE(
REPLACE(
REPLACE(@s,'</StartPart>','>')
,'<StartPart>','<')
,'</EndPart>','>')
,'<EndPart>','</')
SELECT @s-2 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-2 = CONVERT(VARCHAR(MAX), @x)
SET @s-2 =
REPLACE(
REPLACE(
REPLACE(
REPLACE(@s,'</StartPart>','>')
,'<StartPart>','<')
,'</EndPart>','>')
,'<EndPart>','</')
SET @x = CONVERT(XML, @s-2)
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>
July 11, 2013 at 5:50 pm
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
July 12, 2013 at 12:33 am
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...!
July 12, 2013 at 10:32 am
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...?
July 12, 2013 at 11:06 am
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.
July 12, 2013 at 11:36 am
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...?
July 12, 2013 at 12:04 pm
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