Creating xml for SQL Server stored procedure

Question

I have a function to insert an item into the database. It takes a lot of values as input and the values are passed as XML.

Consider a sample item XML:

<ROOT>
    <Item 
        ItemName="CarlsApplication" 
        ItemTypeID="2">
        <TSDefaultDescription DefaultitemDescription="C:\t.text"/>                    
        <ItemSellers>           
            <ComputerObject Alias="" DisplayName="" ServiceName="" UserAccount="" />
            <ComputerObject Alias="" DisplayName="" ServiceName="" UserAccount="" />
        </ItemSellers>
        <ItemOwners>
            <ItemOwner Alias="rafchen" FirstName="Rafael" LastName="Chenkov"/>
        </ItemOwners>
    </Item>
</ROOT>

This has to be passed to stored procedure.

Now, each of these individual values in this XML, I have to extract from somewhere else. I can get the individual values like Item name etc, but how do I organize them into an XML that can be passed?

How do I construct this XML from the values I have?

I guess I will have to make some sort of template with this format and then put variables in that template and fill the variables to prepare the template.

Any help is greatly appreciated.

shA.t · Accepted Answer · 2017-10-11 15:18:43Z

If I understand what you really want, You can use a query like this to generate that XML:

select
    ItemName 'Item/@ItemName',            --> Node:`Item` Attribute: `ItemName` 
    ItemTypeId 'Item/@ItemTypeId',
    cast((
        select 
            Alias 'ComputerObject/@Alias', 
            DisplayName 'ComputerObject/@DisplayName', 
            ServiceName 'ComputerObject/@ServiceName', 
            UserAccount 'ComputerObject/@UserAccount'
        from 
            ItemSellers
        where 
            ItemSellers.ItemId = Item.ItemId
        for xml path('')) as xml) 'Item/ItemSellers',    --> Node:`Item` Sub-Node:`ItemSellers`
    cast((
        select 
            Alias 'ItemOwner/@Alias', 
            FirstName 'ItemOwner/@FirstName', 
            LastName 'ItemOwner/@LastName'
        from 
            ItemOwners
        where 
            ItemOwners.ItemId = Item.ItemId
        for xml path('')) as xml) 'Item/ItemOwners'
from 
    Item
for xml path('ROOT');

SQL Fiddle Demo

Collectives™ on Stack Overflow

Creating xml for SQL Server stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related