Picking strings out of XML-like text

Question

question

GPO asked Mar 13, '12

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: 123 5 fcd13330-4983-4402-b27c-ab01e1a2438e 234 5 90873135-15a7-40e9-bf0d-7152300b66e5 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.

xml string-splitting

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-03-13T06:05:16Z

Usman Butt answered Mar 13, '12

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 )

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt · Mar 13, 2012 at 06:43 AM

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 ;)

1 ·

Usman Butt · Mar 13, 2012 at 06:32 AM

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.

0 ·

GPO · Mar 13, 2012 at 06:32 AM

;-) "....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.

0 ·

GPO · Mar 13, 2012 at 06:35 AM

"...why are you keeping xml column as a NVARCHAR field?..." It's a vendor's database. Who know why they do what they do.

0 ·

question