x

Query xml data store as nvarchar

Hi

I have table which store xml data in below format to a nvarchar(max)column.Want to query this column to get property information.

<Properties><Property Identity="11927069-6957-dfcf-a277-08a784221325">acdcedb7-100c-8c91-d664-4629a218bd94</Property><Property Identity="55124dce-750d-bd54-4b92-d1fddf99adec"></Property><Property Identity="a6b7ea8d-9423-8529-cb21-87ac1a21fdcb"></Property><Property Identity="afb4f9e6-bf48-1737-76ad-c9b3ec325b97">Databases</Property></Properties>

more ▼

asked Aug 10 at 08:17 AM in Default

avatar image

Debasish
59 4 6 9

Has something gone wrong when you pasted XML into this question - that data isn't XML?

Aug 10 at 10:37 AM Kev Riley ♦♦

Sorry some how data is not showing properly .So attached it.link text

xmldata.txt (348 B)
Aug 10 at 10:57 AM Debasish

I've edited your question to correctly show the XML

Aug 10 at 11:32 AM Kev Riley ♦♦

When you say you want Property information - what data do you need (using that XML as an example)?

Aug 10 at 11:33 AM Kev Riley ♦♦

I need the value "Databases" from property Identity

Aug 10 at 12:53 PM Debasish
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

From xml text in question, it looks like there are 2 pieces if information available for each property, namely the Identity attribute and the value of the node. Some of the Property nodes have blank values. Here is the sample script which return 2 columns for each occurrence of the Property node in sample xml:

 declare @v nvarchar(max) = '
     <Properties>
         <Property Identity="11927069-6957-dfcf-a277-08a784221325">acdcedb7-100c-8c91-d664-4629a218bd94</Property>
         <Property Identity="55124dce-750d-bd54-4b92-d1fddf99adec"></Property>
         <Property Identity="a6b7ea8d-9423-8529-cb21-87ac1a21fdcb"></Property>
         <Property Identity="afb4f9e6-bf48-1737-76ad-c9b3ec325b97">Databases</Property>
     </Properties>';
 
 -- the value must be of XML type, the attempts to use 
 --  something like cast(@v as xml).nodes etc. will fail
 declare @x xml = cast(@v as xml);
 
 select
     item.value('.[1]', 'varchar(50)') PropertyText,
     -- for the column below, uniqueidentifier may be used, but
     -- then the values will become spelled out in upper case
     item.value('@Identity[1]', 'varchar(36)') IdentityAttribute
     from @x.nodes('//Property') r(item);

The sample script above produces the following result:

 PropertyText                         IdentityAttribute
 ------------------------------------ ------------------------------------
 acdcedb7-100c-8c91-d664-4629a218bd94 11927069-6957-dfcf-a277-08a784221325
                                      55124dce-750d-bd54-4b92-d1fddf99adec
                                      a6b7ea8d-9423-8529-cb21-87ac1a21fdcb
 Databases                            afb4f9e6-bf48-1737-76ad-c9b3ec325b97

Suppose that there is a table named SomeTable which, sadly, includes the nvarchar type rather than xml type column named SomeColumn. Because it is not possible to cast the the nvarchar column as xml and immediately use .nodes on it, it will take to first write a subselect (or CTE) which has this column cast as xml and only then cross apply. For the sake of completeness, I will assume that the table has some other column, lets call it RecordID, which also needs to be included in the results:

 ;with records as (
     select 
         RecordID, cast(SomeColumn as xml) SomeColumn
         from SomeTable
 )
 select
     RecordID, 
     item.value('.[1]', 'varchar(38)') PropertyText,
     item.value('@Identity[1]', 'varchar(36)') IdentityAttribute
     from records cross apply SomeColumn.nodes('//Property') r(item);

The select statement above will return 3 columns and as many records per each RecordID as the number of Property node occurrences in the value of SomeColumn column.

Hope this helps.

Oleg

more ▼

answered Aug 10 at 12:59 PM

avatar image

Oleg
18.6k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x202

asked: Aug 10 at 08:17 AM

Seen: 17 times

Last Updated: Aug 10 at 12:59 PM

Copyright 2017 Redgate Software. Privacy Policy