Skip to content

Commit 5959a4d

Browse files
icoricicoric
authored
added sql colorizers
and capitalized the keywords
1 parent a9307cc commit 5959a4d

1 file changed

Lines changed: 49 additions & 49 deletions

File tree

‎docs/t-sql/xml/delete-xml-dml.md‎

Lines changed: 49 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -25,8 +25,7 @@ ms.author: genemi
2525

2626
## Syntax
2727

28-
```
29-
28+
```syntaxsql
3029
delete Expression
3130
```
3231

@@ -41,8 +40,8 @@ delete Expression
4140
### A. Deleting nodes from a document stored in an untyped xml variable
4241
The following example illustrates how to delete various nodes from a document. First, an XML instance is assigned to variable of **xml** type. Then, subsequent delete XML DML statements delete various nodes from the document.
4342

44-
```
45-
DECLARE @myDoc xml
43+
```sql
44+
DECLARE @myDoc XML
4645
SET @myDoc = '<?Instructions for=TheWC.exe ?>
4746
<Root>
4847
<!-- instructions for the 1st work center -->
@@ -83,9 +82,9 @@ SELECT @myDoc
8382
### B. Deleting nodes from a document stored in an untyped xml column
8483
In the following example, a **delete** XML DML statement removes the second child element of <`Features`> from the document stored in the column.
8584

86-
```
87-
CREATE TABLE T (i int, x xml)
88-
go
85+
```sql
86+
CREATE TABLE T (i INT, x XML)
87+
GO
8988
INSERT INTO T VALUES(1,'<Root>
9089
<ProductDescription ProductID="1" ProductName="Road Bike">
9190
<Features>
@@ -94,7 +93,7 @@ INSERT INTO T VALUES(1,'<Root>
9493
</Features>
9594
</ProductDescription>
9695
</Root>')
97-
go
96+
GO
9897
-- verify the contents before delete
9998
SELECT x.query(' //ProductDescription/Features')
10099
FROM T
@@ -117,68 +116,69 @@ FROM T
117116

118117
In the example, you first create a table (T) with a typed **xml** column in the AdventureWorks database. You then copy a manufacturing instructions XML instance from the Instructions column in the ProductModel table into table T and delete one or more nodes from the document.
119118

120-
```
121-
use AdventureWorks
122-
go
123-
drop table T
124-
go
125-
create table T(ProductModelID int primary key,
126-
Instructions xml (Production.ManuInstructionsSchemaCollection))
127-
go
128-
insert T
129-
select ProductModelID, Instructions
130-
from Production.ProductModel
131-
where ProductModelID=7
132-
go
133-
select Instructions
134-
from T
119+
```sql
120+
USE AdventureWorks
121+
GO
122+
DROP TABLE T
123+
GO
124+
CREATE TABLE T(
125+
ProductModelID INT PRIMARY KEY,
126+
Instructions XML (Production.ManuInstructionsSchemaCollection))
127+
GO
128+
INSERT T
129+
SELECT ProductModelID, Instructions
130+
FROM Production.ProductModel
131+
WHERE ProductModelID = 7
132+
GO
133+
SELECT Instructions
134+
FROM T
135135
--1) insert <Location 1000/>. Note: <Root> must be singleton in the query
136-
update T
137-
set Instructions.modify('
138-
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
139-
insert <MI:Location LocationID="1000" LaborHours="1000" >
136+
UPDATE T
137+
SET Instructions.modify('
138+
DECLARE namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
139+
INSERT <MI:Location LocationID="1000" LaborHours="1000" >
140140
These are manu steps at location 1000.
141141
<MI:step>New step1 instructions</MI:step>
142142
Instructions for step 2 are here
143143
<MI:step>New step 2 instructions</MI:step>
144144
</MI:Location>
145-
as first
146-
into (/MI:root)[1]
145+
AS first
146+
INTO (/MI:root)[1]
147147
')
148-
go
149-
select Instructions
150-
from T
148+
GO
149+
SELECT Instructions
150+
FROM T
151151

152152
-- delete an attribute
153-
update T
154-
set Instructions.modify('
153+
UPDATE T
154+
SET Instructions.modify('
155155
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
156156
delete(/MI:root/MI:Location[@LocationID=1000]/@LaborHours)
157157
')
158-
go
159-
select Instructions
160-
from T
158+
GO
159+
SELECT Instructions
160+
FROM T
161161
-- delete text in <location>
162-
update T
163-
set Instructions.modify('
162+
UPDATE T
163+
SET Instructions.modify('
164164
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
165165
delete(/MI:root/MI:Location[@LocationID=1000]/text())
166166
')
167-
go
168-
select Instructions
169-
from T
167+
GO
168+
SET Instructions
169+
FROM T
170170
-- delete 2nd manu step at location 1000
171-
update T
172-
set Instructions.modify('
171+
UPDATE T
172+
SET Instructions.modify('
173173
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
174174
delete(/MI:root/MI:Location[@LocationID=1000]/MI:step[2])
175175
')
176-
go
177-
select Instructions
178-
from T
176+
GO
177+
SELECT Instructions
178+
FROM T
179179
-- cleanup
180-
drop table T
181-
go
180+
DROP TABLE T
181+
GO
182182
```
183183

184184
## See Also

0 commit comments

Comments
 (0)