November 24, 2017 at 2:40 pm
Hi everyone,
I'm a NooB at SQL XML creation so hoping you can help me out here.
I have a query like this:
SET @AcuteCare = (
SELECT
DBName AS server,
'True' AS integratedSecurity,
15 AS connectionTimeout,
-1 AS protocol,
4096 AS packetSize,
'False' AS encrypted,
'True' AS selected,
DBName AS cserver
FROM dbo.Instances
WHERE dbname LIKE 'mavscm%db003p'
ORDER BY DBName
FOR XML RAW('ReplaceMe'), ROOT('AcuteCare'), TYPE, ELEMENTS
And it generates this:
<AcuteCare>
<ReplaceMe>
<server>MAVSCMCDB003P</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
<protocol>-1</protocol>
<packetSize>4096</packetSize>
<encrypted>False</encrypted>
<selected>True</selected>
<cserver>MAVSCP</cserver>
</ReplaceMe>
</AcuteCare>
What I actually need for the "ReplaceMe" tag is <value version="5" type="database"> and its closing tag.
Is this possible to do? I'm creating the XML document on the fly by querying data from non-XML tables.
Thanks for any help.
November 24, 2017 at 9:54 pm
This should get you pretty close to it I think. You're looking to populate attributes of an element, which all get prefixed with @ when you name them.
SET @AcuteCare = (
SELECT
5 as '@version',
'database' as '@type',
DBName AS server,
'True' AS integratedSecurity,
15 AS connectionTimeout,
-1 AS protocol,
4096 AS packetSize,
'False' AS encrypted,
'True' AS selected,
DBName AS cserver
FROM dbo.Instances
WHERE dbname LIKE 'mavscm%db003p'
ORDER BY DBName
FOR XML RAW('value'), ROOT('AcuteCare'), TYPE, ELEMENTS
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 24, 2017 at 11:33 pm
Further on Matt's good advice, I suggest you look into using FOR XML PATH instead of RAW, gives you much better control over the output.
😎
If you post the DDL (create table) scripts and some sample data as an insert statement we can take it from there.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply