SQL and XML

• jeff.farr 88997

  SSC Enthusiast

  Points: 160

  More actions

  November 24, 2017 at 2:40 pm

  #333863

  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.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  November 24, 2017 at 9:54 pm

  #1969427

  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?

• Eirikur Eiriksson

  SSC Guru

  Points: 182942

  More actions

  November 24, 2017 at 11:33 pm

  #1969429

  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.