Need to parse values from XML type into SQL View

Question

Post reply

Need to parse values from XML type into SQL View

jchadha

SSC Enthusiast

Points: 129
More actions
November 18, 2013 at 5:09 pm

#301294

Hello
I have a table (JatinApplication) which has a field (properties type NTEXT), which contains string like this:
<ResourceGroupSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.municipalsoftware.com/AppSpec" version="0" availableTimeSubject=""><IsNew>false</IsNew><AlternateGroups /><Members><Member name="CDEAP" priority="1" /><Member name="CDSUP" priority="1" /><Member name="PWCAK" priority="1" /><Member name="PWGIS" priority="1" /><Member name="PWLAB" priority="1" /><Member name="PWMXP" priority="1" /></Members><UserNames /></ResourceGroupSpec>
Now, I need to create / extract the values for tag <Member name>.
I tried experimenting few things, but the most I could go was
- Either store the text in a variable
DECLARE @x VarChar(MAX)
Set @x = (Select Properties as Col1 from JatinApplication where Category like 'ResourceGroup' and ObjectName = 'Admin' )
Print @x
which gave me this
<?xml version="1.0" encoding="utf-16"?>
<ResourceGroupSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="0" availableTimeSubject="" xmlns="http://www.municipalsoftware.com/AppSpec">
<IsNew>false</IsNew>
<AlternateGroups />
<Members>
<Member name="CDEAP" priority="1" />
<Member name="CDSUP" priority="1" />
<Member name="PWCAK" priority="1" />
<Member name="PWGIS" priority="1" />
<Member name="PWLAB" priority="1" />
<Member name="PWMXP" priority="1" />
</Members>
<UserNames />
</ResourceGroupSpec>
- Or ended up getting nothing
Declare @handle int
DECLARE @Cols VARCHAR(MAX)
DECLARE @Xml AS XML = (Select
CAST (replace(CAST(Properties as nvarchar(max)), 'utf-8', 'utf-16') AS xml)
as Col1 from JatinApplicationwhere Category like 'ResourceGroup' and ObjectName = 'Admin' )
Exec sp_xml_PrepareDocument @handle output, @xml
Select @handle
No Column Name
--------------
113
I need to write a script which can shred the data into SQL View column , something like this
Code
------
CDEAP
CDSUP
PWCAK
PWGIS
PWLAB
PWMXP
Any help with , is highly appreciated.
Thanks in advance.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

declare @properties varchar(max)='<?xml version="1.0" encoding="utf-16"?>

<ResourceGroupSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="0" availableTimeSubject="" xmlns="http://www.municipalsoftware.com/AppSpec">

<IsNew>false</IsNew>

<AlternateGroups />

<Members>

<Member name="CDEAP" priority="1" />

<Member name="CDSUP" priority="1" />

<Member name="PWCAK" priority="1" />

<Member name="PWGIS" priority="1" />

<Member name="PWLAB" priority="1" />

<Member name="PWMXP" priority="1" />

</Members>

<UserNames />

</ResourceGroupSpec>';

with xmlnamespaces ( 'http://www.w3.org/2001/XMLSchema-instance' as xsi, default 'http://www.municipalsoftware.com/AppSpec')

select nd.value('@name','char(5)') as MemberName

from (select cast(replace(@properties,'utf-16','utf-8') as xml) as properties) a

cross apply properties.nodes('/ResourceGroupSpec/Members/Member') x(nd)

Just replace the handling of the variable @properties with your table column.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

jchadha SSC Enthusiast Points: 129 More actions · Answer 2

See, I want that the xml should ALWAYS be linked to the table JatinApplication.

What your script does is , it is creating a stand alone data from the xml, if there is anew value in the table (JatinApplication), this view will not automatically refreshed, I will again have to incorporate that new value in the script, which defeats the purpose for creating a real time view on xml type field.

jchadha SSC Enthusiast Points: 129 More actions · Answer 3

Thanks!!!!!!!!!!!!!!!!!!! I go it.....

You guys ROCK:-)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

Yes, that is why I said "Just replace the handling of the variable @properties with your table column"....

You haven't provided any scripts for table creation or sample data, so I just showed you how to handle the xml with the namespaces.

I leave it to you to turn that into a TVF so that you can apply it to your table.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

jchadha SSC Enthusiast Points: 129 More actions · Answer 5

So, now , if I have to create a VIEW off of this script, how should I do it because it is returning an error when I write like this:

Create View viewJatin as

Declare.....

mister.magoo SSC-Forever Points: 47068 More actions · Answer 6

I would suggest you start by learning about VIEWS here : CREATE VIEW (Transact-SQL)

Then do something like this:

create view viewJatin

AS

with xmlnamespaces ( 'http://www.w3.org/2001/XMLSchema-instance' as xsi, default 'http://www.municipalsoftware.com/AppSpec')

select nd.value('@name','char(5)') as MemberName

from

(

select cast(replace(properties,'utf-16','utf-8') as xml) as propertiesXML

from JatinApplication

where Category like 'ResourceGroup'

and ObjectName = 'Admin'

) a

cross apply propertiesXML.nodes('/ResourceGroupSpec/Members/Member') x(nd)

But, that seems like a pretty worthless view, so I suspect you actually need something different to that, maybe some more columns included in the output and without the specific WHERE clause?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

jchadha SSC Enthusiast Points: 129 More actions · Answer 7

Thanks for tip, but I have never , in my life tread into the XML parsing territory.

After I posted, I did realized that I cannot "declare" a variable inside view, my

but your script did the trick for, I have the view created and tons of thanks for your help!:-)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 8

you are welcome:-D

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);