declare @GoodsList xml = '
<GoodsList xmlns="urn:Wide_World_Importers/schemas/Goods">
 <Goods Name="GoodsA" Category="clothes" Price="15.1" />
 <Goods Name="GoodsB" Category="Gas" Price="3.5" />
 <Goods Name="GoodsC" Category="Stationary" Price="2.1" />
</GoodsList>';

WITH XMLNAMESPACES( DEFAULT 'urn:Wide_World_Importers/schemas/Goods')
SELECT
Goods.value('./@Name','varchar(100)') as Name,
Goods.value('./@Category','varchar(20)') as Category,
Goods.value('./@Price','money') as [Value]
FROM @GoodsList.nodes('/GoodsList/Goods') GoodsList(Goods)

What is important:

  • The ‘declare @Goodslist …’ statement must end with a semicolon, otherwise the parser cannot say which statement uses the ‘with’ clause.
  • The ‘@GoodsList.nodes(‘/GoodsList/Goods’) GoodsList(Goods)’ must define a name for the query source

The query returns:

GoodsA	clothes	15.10
GoodsB	Gas	3.50
GoodsC	Stationary	2.10

The xml document can be stored in an xml column in a table. In that case it is important to note that the nodes() function returns a table-valued value, which means the APPLY operator must be used.

Example:

CREATE TABLE [dbo].[XmlDocument](
	[id] [int] IDENTITY(1,1) NOT NULL primary key clustered,
	[data] [xml] NOT NULL
)>

creates a table to store the xml documents.

insert XmlDocument( data )
values
(
'
<GoodsList xmlns="urn:Wide_World_Importers/schemas/Goods">
 <Goods Name="GoodsA" Category="clothes" Price="15.1" />
 <Goods Name="GoodsB" Category="Gas" Price="3.5" />
 <Goods Name="GoodsC" Category="Stationary" Price="2.1" />
</GoodsList>
'
)

inserts one xml document in the table.

The query is:

WITH XMLNAMESPACES( DEFAULT 'urn:Wide_World_Importers/schemas/Goods')
Select
Goods.value('./@Name','varchar(100)') as Name,
Goods.value('./@Category','varchar(20)') as Category,
Goods.value('./@Price','money') as [Value]
from
XmlDocument
cross apply data.nodes('/GoodsList/Goods') as T(Goods)

Make sure you do not type Nodes instead of nodes (a common mistake), otherwise SQL Server reports an error.

The query returns:
GoodsA clothes 15.10
GoodsB Gas 3.50
GoodsC Stationary 2.10

Now insert a new document in the table:

insert XmlDocument( data )
values
(
'
<GoodsList xmlns="urn:Wide_World_Importers/schemas/Goods">
 <Goods Name="GoodsD" Category="clothes" Price="40" />
 <Goods Name="GoodsF" Category="Gas" Price="100" />
 <Goods Name="GoodsN" Category="Stationary" Price="5.4" />
</GoodsList>
'
)

The query returns:
GoodsA clothes 15.10
GoodsB Gas 3.50
GoodsC Stationary 2.10
GoodsD clothes 40.00
GoodsF Gas 100.00
GoodsN Stationary 5.40

If you want to retrieve only the names, use the following query:

WITH XMLNAMESPACES( DEFAULT 'urn:Wide_World_Importers/schemas/Goods')
Select 
Goods.query('<Names x="{./@Name}" />')
--Goods.query('.')
from
XmlDocument
cross apply data.nodes('/GoodsList/Goods') as T(Goods)

Advertisements