Yes, it can work on an XML column too. Here is an example

DECLARE @t TABLE (id int, data XML)

INSERT INTO @t(id, data) SELECT 1, '

...

Glad to know it helped.

I have written a series of XQuery tutorials at my blog site, in case you need further help.

regards

Jacob

andrew.sims (7/22/2009)

---

Nice article.

Would it also be possible to look at the same processes where the XML data is arranged like...

Jacob

NY

SQL Server

Steve

SQL Server

...

mg (7/22/2009)

---

In example 10 where you delete the “city” attribute where the “team” is “SQL Server” is there any way to pass in the “SQL Server” as a variable instead...

Hi Yair,

All these are TSQL examples and hence you can run them in SQL Server Management Studio.

regards

Jacob

Here is an example that I just tried and it works

DECLARE @x XML(CustomerSchema), @y XML

SELECT @y = 'a'

BEGIN TRY

SELECT @x = @y

END TRY

BEGIN CATCH

RAISERROR('Hey, this is an invalid XML',16,1)

END CATCH

/*

OUTPUT:

Msg...

The regular expression language is documented here: http://www.w3.org/TR/xmlschema-2/#regexs

The documentation is not very easy to understand but you might be able to spot the problem with your syntax.

I have...

I wanted to come up with a PART 2 which focuses on using the results in an ASP.NET page (display paging information etc). Well, now I think there are enough...

Here is another version of the code that does not use the 'parent node accessor'

DECLARE @t TABLE (data XML)

INSERT INTO @t(data) SELECT '

[LocalGroups]

[LocalGroup]

[Name]Administrators[/Name]

[Description]Administrators have complete and...

...and this example shows how to achieve it with a CROSS APPLY.

SELECT

p.value('@Id[1]','int') AS PropertyId,

c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,

c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber

FROM @x.nodes('//Property') n(p)

CROSS APPLY p.nodes('PhoneList/Phone') t(c)

XML data type can store XML DOCUMENTS (having exactly 1 root element) and XML CONTENT (having 0 or more top level elements). The default is CONTENT and hence it...

bdba,

I altered the query a bit. Here is the new version

SELECT

p.value('@Name', 'varchar(15)') AS ProductName,

p.value('@ID', 'int') AS ProductID,

i.value('@Keyname', 'varchar(15)') AS ItemName,

i.value('@Keyvalue', 'varchar(15)') AS ItemValue

FROM @MyXML.nodes('/MyXML/Product') x(p)

CROSS APPLY p.nodes('Item') y(i)

/*

ProductName ...

Hi Charles, Thank you for pointing this out. I have corrected it, however, it might take a few days before the new version will be published.

Thanks

Jacob

Bishal,

I would recommend using .NET XML libraries to perform this, instead of trying to do this in TSQL. If ever you want to do it in TSQL, it would involve...

This article focuses on passing a table (a set of tabular data) to a stored procedure for further processing. It does not refer to passing one of the tables of...