April 23, 2015 at 4:57 pm
Hi All,
I have a sql query:
SELECT
'5' AS 'value/@version',
'database' AS 'value/@type',
'master' AS 'value/name',
LTRIM(RTRIM(( [Server Name] ))) AS 'value/server',
'True' AS 'value/integratedSecurity',
15 AS 'value/connectionTimeout',
4096 AS 'value/packetSize',
'False' AS 'value/encrypted',
'True' AS 'value/selected',
LTRIM(RTRIM(( [Server Name] ))) AS 'value/cserver'
FROM dbo.RedGateServerList
FOR XML PATH(''), ELEMENTS
I need to add some header information to the beginning of the query:
<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34--><multiScriptApplication version="2" type="multiScriptApplication"><databaseLists type="List_databaseList" version="1">
Everything I have tried ends up as a failure, usually compile issues. Can someone tell me what I am doing wrong.
My goal here is to be able to automare a configuration file for multiscript so I can keep my server list up to date.
Thanks,
April 24, 2015 at 1:19 am
Quick solution that should get you passed this hurdle, some comments in the code.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* XML variable for the elements to insert */
DECLARE @XML_INSERT XML = NULL;
/* Parent XML host the insert */
DECLARE @XML XML = N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34-->
<multiScriptApplication version="2" type="multiScriptApplication">
<databaseLists type="List_databaseList" version="1">
</databaseLists>
</multiScriptApplication>
'
/* Pseudo Table */
;WITH dbo_RedGateServerList AS
(
SELECT * FROM
(VALUES
(1 ,N'DB_SERVER_001')
,(2 ,N'DB_SERVER_002')
,(3 ,N'DB_SERVER_003')
,(4 ,N'DB_SERVER_004')
,(5 ,N'DB_SERVER_005')
,(6 ,N'DB_SERVER_006')
,(7 ,N'DB_SERVER_007')
,(8 ,N'DB_SERVER_008')
,(9 ,N'DB_SERVER_009')
) AS X([Server ID],[Server Name])
)
/* Construct the insert */
SELECT @XML_INSERT =
(SELECT
N'5' AS N'value/@version',
N'database' AS N'value/@type',
N'master' AS N'value/name',
LTRIM(RTRIM(( [Server Name] ))) AS N'value/server',
N'True' AS N'value/integratedSecurity',
15 AS N'value/connectionTimeout',
4096 AS N'value/packetSize',
N'False' AS N'value/encrypted',
N'True' AS N'value/selected',
LTRIM(RTRIM(( [Server Name] ))) AS N'value/cserver'
FROM dbo_RedGateServerList
FOR XML PATH(''), ELEMENTS, TYPE)
/* Plug the insert into the hosting XML */
SET @XML.modify('
insert sql:variable("@XML_INSERT")
into (multiScriptApplication/databaseLists)[1]');
/* Unicode output whith the xml version and encoding
Note: that when converted to XML in SQL Server, it strips
this out as it is the defaults for UTF-xx
*/
SELECT
N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>' + NCHAR(13) + NCHAR(10)
+ CONVERT(NVARCHAR(MAX),@XML,3);
Result
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34-->
<multiScriptApplication version="2" type="multiScriptApplication">
<databaseLists type="List_databaseList" version="1">
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_001</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_001</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_002</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_002</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_003</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_003</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_004</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_004</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_005</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_005</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_006</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_006</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_007</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_007</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_008</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_008</cserver>
</value>
<value version="5" type="database">
<name>master</name>
<server>DB_SERVER_009</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>DB_SERVER_009</cserver>
</value>
</databaseLists>
</multiScriptApplication>
April 24, 2015 at 2:11 am
First build XML then cast to NVarchar and add headers, because when processing XML type MSSS treats <xml ..> not as part of data but as a directive specifying following text encoding .
SELECT CAST(N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34-->' AS NVARCHAR(MAX))
+ CAST(
(-- all XML but header
SELECT
'2' as '@version',
(SELECT'List_database' as '@type',
'1' as '@version',
(SELECT
'5' AS '@version',
'database' AS '@type',
'master' AS 'name',
LTRIM(RTRIM(( [Server Name] ))) AS 'server',
'True' AS 'integratedSecurity',
15 AS 'connectionTimeout',
4096 AS 'packetSize',
'False' AS 'encrypted',
'True' AS 'selected',
LTRIM(RTRIM(( [Server Name] ))) AS 'cserver'
FROM -- moke table
(values ('server 1'), ('server 2')) RedGateServerList([Server Name])
FOR XML PATH('value'), TYPE)
FOR XML PATH('ListdatabaseLists'),TYPE)
FOR XML PATH('multiScriptApplication')
) AS NVARCHAR(MAX));
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply