1

I am creating an xml file using a SELECT statement and I am having trouble adding an extra node into the document.

The current structure created is this:

<DBObjectsToUpdate>
    <TablesToAdd>...</TablesToAdd>
    <TableObjectsToDelete> ...</TableObjectsToDelete>
    <Views>
        <View> </View>
    </Views>
    <StoredProcedures>   
        <StoredProc> </StoredProc>
    </StoredProcedures>
</DBObjectsToUpdate>

What I want to do is add a surrounding node around the 'Views' and 'StoredProcedures'. So it will look like this:

<DBObjectsToUpdate>
        <TablesToAdd>...</TablesToAdd>
        <TableObjectsToDelete> ...</TableObjectsToDelete>
        <TableObjectsToAdd> 
            <Views>
                <View> </View>
            </Views>
            <StoredProcedures>   
                <StoredProc> </StoredProc>
            </StoredProcedures>
        </TableObjectsToAdd>
    </DBObjectsToUpdate>

This is the relevant parts of the select statement:

SELECT 
    [Table].TableName AS "@TName", SchemaName AS "@Schema", 
    TextImageFileGroup AS "@TextImageOnFileGroup",
    ..bunch of stuff that is not important for this question
FROM
    ( a select statement to get the tables) AS [Table]

(--Returns all of the Triggers - Currently there are no triggers
SELECT 
    [Trig].TriggerName AS "@TriggerName",  
    [Trig].TrigDefinition AS "@TrigDefinition"
FROM 
    (SELECT DISTINCT S7.TableName, S7.TriggerName, TrigDefinition 
     FROM #SourceDBObjects S7 
     JOIN #TableObjectsToAdd T7 ON S7.TableName = T7.TableName 
                                AND S7.TriggerName = T7.TriggerName 
                                AND T7.TriggerName IS NOT NULL) AS [Trig]
     WHERE 
         [Table].TableName = [Trig].TableName
     FOR XML PATH('Trigger'), TYPE) AS [Triggers]
FROM 
    (SELECT DISTINCT T.TableName, S.SchemaName, S.TextImageFileGroup
     FROM #TableObjectsToAdd T 
     JOIN #SourceDBObjects S ON T.TableName = S.TableName)  AS [Table]
ORDER BY 
    "@TName"
FOR XML PATH ('Table'), TYPE) as [TableObjectsToAdd],
            (--Returns all of the check constrains for table
                    SELECT [CkCon].CheckName AS "@CkName", [CkCon].CkDefinition AS "@CkDefinition", [CkCon].IsCkDisabled AS "@IsCkDisabled"
                    FROM (SELECT DISTINCT T5.TableName, T5.CheckName, CkDefinition, IsCkDisabled
                          FROM #TargetDBObjects T5 JOIN #TableObjectsToDelete D5 ON T5.TableName = D5.TableName AND T5.DefaultName = T5.CheckName AND D5.CheckName IS NOT NULL) AS [CkCon]
                    WHERE [Table].TableName = [CkCon].TableName
                    FOR XML PATH('Check'),
                    TYPE
                ) AS [CheckConstraints]
        FROM (
                SELECT DISTINCT D.TableName, T.SchemaName, T.TextImageFileGroup
                FROM #TableObjectsToDelete D JOIN #TargetDBObjects T ON T.TableName = D.TableName
             )  AS [Table]
        ORDER BY "@TName"
        FOR XML PATH ('Table'), TYPE)
        as [TableObjectsToDelete],
        (   ------VIEWS NODE------
             SELECT ObjectName AS "@VName", REPLACE(ObjectText, '''', '''''')  AS "@VDefinition"
             FROM #SourceViewsSPFunctionsToAdd 
             WHERE ObjectType = 'View'
             FOR XML PATH ('View'), TYPE
         ) AS [Views],
         (   ------STORED PROCEDURES NODE------
             SELECT ObjectName AS "@SPName", REPLACE(ObjectText, '''', '''''')  AS "@SPDefinition"
             FROM #SourceViewsSPFunctionsToAdd 
             WHERE ObjectType = 'StoredProcedure'
             FOR XML PATH ('StoredProcedure'), TYPE
          ) AS [StoredProcedures],
FOR XML PATH(''),
ROOT('DBObjectsToUpdate')

How do I encapsulate the views and stored procedures nodes into an outside node with has not elements or attributes?

1 Answer 1

1

I figured this out...Encapsulate the nodes using a SELECT FOR XML PATH(''), TYPE) AS [NameOfNode]

So the part of the select statement in question looks like this: ...

(SELECT
 (   ------VIEWS NODE------
             SELECT ObjectName AS "@VName", REPLACE(ObjectText, '''', '''''')  AS "@VDefinition"
             FROM #SourceViewsSPFunctionsToAdd 
             WHERE ObjectType = 'View'
             FOR XML PATH ('View'), TYPE
         ) AS [Views],
         (   ------STORED PROCEDURES NODE------
             SELECT ObjectName AS "@SPName", REPLACE(ObjectText, '''', '''''')  AS "@SPDefinition"
             FROM #SourceViewsSPFunctionsToAdd 
             WHERE ObjectType = 'StoredProcedure'
             FOR XML PATH ('StoredProcedure'), TYPE
          ) AS [StoredProcedures],
 FOR XML PATH (''), TYPE) as [DBObjectsToAdd]
Sign up to request clarification or add additional context in comments.

1 Comment

Greate! I hope you get the self-learner-badge. I'll give it a start: +1 :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.