Extract data from XML column - tried everything

1

I have tried all suggestions previously posted here but still cannot extract data from my Xml column. In the data below I want to pull out the value for Text1Size50 which is John Doe

Table Name = tblDataPermit

Column Name = XmlDataField

Xml Column contains:

<XmlDataField xmlns="http://MgmsSchema/Business_Information_Detail_Entry" ApplicationNumber="00001040"><Text1Size50>John Doe</Text1Size50> <Text2Size50>Office</Text2Size50><Text3Size50>Center of Truth and Light</Text3Size50>

I have tried these plus others:
SELECT ref.value('(/XmlDataField[Name="Description"]/Value)[1]','varchar(max)') AS Result
FROM tblDataPermit
CROSS APPLY tblDataPermit.XmlDataField.nodes ('/Text1Size50') R(ref)
WHERE tblDataPermit.PermitNumber = '14-007'

SELECT tblDataPermit.XmlDataField.value('(/XmlDataField[Name="Text1Size50"]/Value)[1]','varchar(max)')
FROM tblDataPermit
WHERE tblDataPermit.PermitNumber = '14-007'

2

that namespace is messing you up

create table #tblDataPermit(XmlDataField xml)

insert into #tblDataPermit

select '<XmlDataField xmlns="http://MgmsSchema/Business_Information_Detail_Entry" 
ApplicationNumber="00001040"><Text1Size50>John Doe</Text1Size50>
<Text2Size50>Office</Text2Size50><Text3Size50>Center of Truth and Light</Text3Size50></XmlDataField>'


select XmlDataField.value('(//*[local-name()="Text2Size50"])[1]', 'nvarchar(max)') 
  from #tblDataPermit a


drop table #tblDataPermit
2 Likes
3

After visiting 12 web sites and several forums you are the first to solve this issue. Thank you!
Now I'm trying to figure out how to have this data appear in a text field in Microsoft Sql Server Report Builder.

4

Are you using Microsoft Report Builder?

5

Yes, 3.0

6

ok can you please post a new question: How to create a Report using 3.0?

7

Will do