XML : Another example of generating XML

declare @branch_code nchar(3), @branch_id int, @tillnum int, @i int

select @branch_code = N’101′, @branch_id = 3, @tillnum = 1, @i = 10

set @xml = ‘<sale_or_return_ordering>
</sale_or_return_ordering>’
— key
select @key =
(
select   @branch_code as branch_code,
@branch_id as branch_id,
order_number,
@tillnum as tillnum
from  #Orders [key]
where seq = @i
for xml auto
)

— security
set @security = ‘<security username=”sa” />’

— preferences
set @preferences = ‘<preferences prefstylecode=”PROD_CODE” prefskucode=”SKU_CODE” prefstylecodeindex=”1″ prefskucodeindex=”1″ />’

set @xml.modify(‘insert sql:variable(“@preferences”) as first into (/sale_or_return_ordering)[1]’)

set @xml.modify(‘insert sql:variable(“@security”) as first into (/sale_or_return_ordering)[1]’)

set @xml.modify(‘insert sql:variable(“@key”) as first into (/sale_or_return_ordering)[1]’)

select @xml

Advertisements

XML : Read Values from XML in SQL Server

select  orders.item.value(N’@description’, ‘nvarchar(1000)’) as description ,
                        orders.item.value(N’@quantiy_ordered’,
                                          ‘nvarchar(1000)’) as quantiy_ordered ,
                        orders.item.value(N’@sizes_description_short’,
                                          ‘nvarchar(1000)’) as sizes_description_short ,
                        orders.item.value(N’@sku_code_composite’,
                                          ‘nvarchar(30)’) as sku_code_composite ,
                        orders.item.value(N’@selling_price’, ‘money’) as selling_price ,
                        orders.item.value(N’@quantiy_ordered’, ‘int’) as quantiy_ordered ,
                        orders.item.value(N’@quantity_short_picked’, ‘int’) as quantity_short_picked ,
                        orders.item.value(N’@epos_rr_units_sold’, ‘int’) as epos_rr_units_sold ,
                        orders.item.value(N’@quantity_returned’, ‘int’) as quantity_returned ,
                        orders.item.value(N’@quantity_adjustment’, ‘int’) as quantity_adjustment ,
                        orders.item.value(N’@quantity_balance’, ‘int’) as quantity_balance ,
                        orders.item.value(N’@pb0′, ‘int’) as pb0 ,
                        orders.item.value(N’@pb1′, ‘int’) as pb1 ,
                        orders.item.value(N’@pb2′, ‘nvarchar(100)’) as pb2
                from    @xml.nodes(‘/sale_or_return_ordering/list_items/row’) orders ( item );

XML : Insert an element into XML in SQL Server

declare @xml xml, @xml1 xml

set @xml1 =

<list_items>
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010035033030″ selling_price=”44.00″ quantiy_ordered=”200″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010111010006045014″ selling_price=”100.00″ quantiy_ordered=”10″ />
</list_items>

set @xml =

<sale_or_return_ordering>
<key branch_code=”001″ />
<header module_state=”CLOSED” account_number=”0000578″ />
<list_billing_address>
<row billing_address=”MR” pb0=”Title” pb1=”1″ />
<row billing_address=”Hannah” pb0=”First Name” pb1=”2″ />
<row billing_address=”Palmer” pb0=”Surname” pb1=”3″ />
<row billing_address=”50″ pb0=”Address line 1″ pb1=”4″ />
<row billing_address=”Ward Road” pb0=”Address line 2″ pb1=”5″ />
</list_billing_address>
</sale_or_return_ordering>

set @xml.modify(‘insert sql:variable(“@xml1”) as first into (/sale_or_return_ordering)[1]’)

select @xml

XML : Remove an element from xml in SQL Server

declare @xml xml

set @xml =

<sale_or_return_ordering>
<key branch_code=”001″ />
<header module_state=”CLOSED” account_number=”0000578″ />
<list_items>
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010035033030″ selling_price=”44.00″ quantiy_ordered=”100″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010111010006045014″ selling_price=”100.00″ quantiy_ordered=”1″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010026003014″ selling_price=”-50.00″ quantiy_ordered=”1″ />
</list_items>
<list_billing_address>
<row billing_address=”MR” pb0=”Title” pb1=”1″ />
<row billing_address=”Hannah” pb0=”First Name” pb1=”2″ />
<row billing_address=”Palmer” pb0=”Surname” pb1=”3″ />
<row billing_address=”50″ pb0=”Address line 1″ pb1=”4″ />
<row billing_address=”Ward Road” pb0=”Address line 2″ pb1=”5″ />
</list_billing_address>
</sale_or_return_ordering>

set @xml.modify(‘delete sale_or_return_ordering/list_items/row’)

select @xml

DBA : Get table locks in SQL Server

— Note:
— Edit the script below to change the database name and the table name.

select      o.name,*

from  [DatabaseName].sys.dm_tran_locks  l

join  [DatabaseName].sys.objects        o     on    l.resource_associated_entity_id = o.object_id

where resource_type = ‘OBJECT’

and resource_database_id = DB_ID(‘[DatabaseName]’)

–and o.name like ‘[table name]’