x

Picking strings out of XML-like text

I have some XML-like data that is stored as nvarchar(max). A cell from the table in which it is stored, looks a bit like this:

   <Items>
        <Item Name="Fred">
           <ItemReference>123</ItemReference>
           <ItemType>5</ItemType>
           <ItemID>fcd13330-4983-4402-b27c-ab01e1a2438e</ItemID>
        </Item>
        <Item Name="Boo">
           <ItemReference>234</ItemReference>
           <ItemType>5</ItemType>
           <ItemID>90873135-15a7-40e9-bf0d-7152300b66e5</ItemID>
        </Item>
    </Items>

I want to be able to split the string up and only pick bits of the data out (and dump the results into a table) so that it looks (for example) like this.

Item_name    Item_type
Fred         5
Boo          5

I should probably add that some of the elements I want to split out could easily be over 8000 characters in length in their "post-split" state.

more ▼

asked Mar 13, 2012 at 02:38 AM in Default

GPO gravatar image

GPO
2.3k 34 37 41

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Seems pretty straight forward to me OR Am I missing something?

DECLARE @xmltable TABLE ( ID INT IDENTITY, XMLCOL NVARCHAR(MAX) )

INSERT @xmltable ( [XMLCOL] ) SELECT '<Items> <Item Name="Fred"> <ItemReference>123</ItemReference> <ItemType>5</ItemType> <ItemID>fcd13330-4983-4402-b27c-ab01e1a2438e</ItemID> </Item> <Item Name="Boo"> <ItemReference>234</ItemReference> <ItemType>5</ItemType> <ItemID>90873135-15a7-40e9-bf0d-7152300b66e5</ItemID> </Item> </Items>'

SELECT Item_name , Item_type FROM ( SELECT CAST(XMLCOL AS XML) XMLCOL FROM @xmltable ) xmltable CROSS APPLY XMLCOL.nodes('./Items/Item') x ( col ) CROSS APPLY ( SELECT x.col.value('@Name', 'varchar(50)') , x.col.value('(./ItemType)[1]', 'varchar(50)') ) ColumnAlias ( Item_name, Item_type )
more ▼

answered Mar 13, 2012 at 06:05 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

BTW, why are you keeping xml column as a NVARCHAR field? Is it to keep it available across multiple servers? If you cannot change it then for future work, you can make a VIEW which would have this field CASTed as XML.
Mar 13, 2012 at 06:32 AM Usman Butt
;-) "....pretty straight forward..." Looks like I'm going to need the "for dummies" primer in that XML querying language! I don't get it at all.
Mar 13, 2012 at 06:32 AM GPO
"...why are you keeping xml column as a NVARCHAR field?..." It's a vendor's database. Who know why they do what they do.
Mar 13, 2012 at 06:35 AM GPO
hmmm...but still Are you not one of the stakeholders ;). As far as the straightforwardness was concerned, I meant it should be treated as reading the xml. The words like "could easily be over 8000 characters in length in their "post-split" state" were making me think that may be I have not understand the question properly :) But, rest assured me too is not an expert in XML. I have seen people doing some crazy stuff with that, and I never seem to have the time to understand ;)
Mar 13, 2012 at 06:43 AM Usman Butt
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x150
x6

asked: Mar 13, 2012 at 02:38 AM

Seen: 1296 times

Last Updated: Mar 13, 2012 at 06:43 AM