c# - How to ensure the SQL is able to read all XML tag data -

c# - How to ensure the SQL is able to read all XML tag data -

i have next xml info in sql table column:

<root> <physicians> <name></name> <picture></picture> <gender></gender> <langaccept>english</langaccept> <langaccept>spanish</langaccept> (can appear more times) <insaccept>aetna</insaccept> <insaccept>bcbs</insaccept> (can appear more times) <specialty></specialty> <specialty2></specialty2> <specialty3></specialty3> </physicians> </root>

the langaccept , insaccept can appear multiple times, , there no way know how many times.

i have next sql query not taking business relationship 'langaccept' , 'insaccept' tags:

declare @strprovider varchar(200) set @strprovider = '' --the provider dropdownlist declare @strspecialty varchar(200) set @strspecialty = '' --the specialty dropdownlist declare @strlocation varchar(200) set @strlocation = '' --the location dropdownlist declare @strgender varchar(200) set @strgender = '' --the gender dropdownlist declare @strinsurance varchar(200) set @strinsurance = '' --the insurance dropdownlist declare @strlanguage varchar(200) set @strlanguage = '' --the language dropdownlist select [content_title] [physician name] , [content_status] [status] , cast([content_html] xml).value('(root/physicians/picture/img/@src)[1]','varchar(255)') [image] , dbo.usp_clearhtmltags(convert(nvarchar(600), cast([content_html] xml).query('root/physicians/gender'))) [gender] , cast([content_html] xml).query('/root/physicians/officelocations/office1/a') [office1] , cast([content_html] xml).query('/root/physicians/officelocations/office2/a') [office2] , cast([content_html] xml).query('/root/physicians/officelocations/office3/a') [office3] , cast([content_html] xml).query('/root/physicians/officelocations/office4/a') [office4] , cast ([content_html] xml).query('/root/physicians/specialty/a') [specialty1] , cast ([content_html] xml).query('/root/physicians/specialty2/a') [specialty2] [mydb].[dbo].[content] [folder_id] = '188' , (content_html '%<gender>%'+ @strgender+'%</gender>%') , (content_html '%'+@strspecialty+'%') , (content_html '%'+@strlocation+'%') , (content_status = 'a') order [content_title]

i taking info , writing repeater in asp.net page using c# code-behind.

how can modify sql query takes value each langaccept , insaccept tag (as many times appears).

you can handle number of nodes might repeat - mind you, create numerous rows single entry <physician>.

try this:

declare @content table (id int not null, xmldata xml) insert @content values(1, '<root> <physicians> <name>dr. excellent</name> <picture></picture> <gender>male</gender> <langaccept>english</langaccept> <langaccept>spanish</langaccept> <insaccept>aetna</insaccept> <insaccept>bcbs</insaccept> <specialty></specialty> <specialty2></specialty2> <specialty3></specialty3> </physicians> </root>') select id, physicianname = xc.value('(name)[1]', 'varchar(50)'), gender = xc.value('(gender)[1]', 'varchar(50)'), langspoken = xlang.value('.', 'varchar(20)'), insaccepted = xins.value('.', 'varchar(50)') @content cross apply xmldata.nodes('/root/physicians') xt(xc) cross apply xc.nodes('langaccept') xt2(xlang) cross apply xc.nodes('insaccept') xt3(xins)

by using .nodes() on both langaccept , insaccept within <physician> node, defined values - end several relational rows single <physican> node:

update: info own existing table, utilize this:

select id, physicianname = xc.value('(name)[1]', 'varchar(50)'), gender = xc.value('(gender)[1]', 'varchar(50)'), langspoken = xlang.value('.', 'varchar(20)'), insaccepted = xins.value('.', 'varchar(50)') [mydb].[dbo].content cross apply cast(content_html xml).nodes('/root/physicians') xt(xc) cross apply xc.nodes('langaccept') xt2(xlang) cross apply xc.nodes('insaccept') xt3(xins)

c# asp.net sql-server xml