Как обеспечить, чтобы SQL мог читать все данные тега XML

У меня есть следующие данные XML в столбце таблицы SQL:

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

langAccept и insAccept могут появляться несколько раз, и нет никакого способа узнать, сколько раз.

У меня есть следующий SQL-запрос, который в настоящее время не учитывает теги «langAccept» и «insAccept»:

 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] AS [Physician Name] , [content_status] AS [Status] , CAST([content_html] AS XML).value('(root/Physicians/picture/img/@src)[1]','varchar(255)') AS [Image] , dbo.usp_ClearHTMLTags(CONVERT(nvarchar(600), CAST([content_html] AS XML).query('root/Physicians/gender'))) AS [Gender] , CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office1/a') AS [Office1] , CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office2/a') AS [Office2] , CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office3/a') AS [Office3] , CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office4/a') AS [Office4] , CAST ([content_html] AS XML).query('/root/Physicians/specialty/a') AS [Specialty1] , CAST ([content_html] AS XML).query('/root/Physicians/specialty2/a') AS [Specialty2] FROM [MYDB].[dbo].[content] WHERE [folder_id] = '188' AND (content_html LIKE '%<gender>%'+ @strGender+'%</gender>%') AND (content_html LIKE '%'+@strSpecialty+'%') AND (content_html LIKE '%'+@strLocation+'%') AND (content_status = 'A') ORDER BY [content_title] 

Я буду брать эти данные и записывать ретранслятор на моей странице ASP.net, используя C # в качестве кода.

Как я могу изменить свой SQL-запрос так, чтобы он langAccept значение для каждого langAccept и insAccept (столько раз, сколько он появляется).

Solutions Collecting From Web of "Как обеспечить, чтобы SQL мог читать все данные тега XML"

Вы можете обрабатывать любое количество узлов, которые могли бы повториться, но помните, что это всегда создает множество строк для одной записи <Physician> .

Попробуй это:

 DECLARE @Content TABLE (ID INT NOT NULL, XmlDAta XML) INSERT INTO @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)') FROM @Content CROSS APPLY XmlData.nodes('/root/Physicians') AS XT(XC) CROSS APPLY XC.nodes('langAccept') AS XT2(XLang) CROSS APPLY XC.nodes('insAccept') AS XT3(XIns) 

Используя .nodes() как для langAccept и для insAccept внутри узла <Physician> , вы получаете все определенные значения, но вы получаете несколько реляционных строк для одного узла <Physican> :

введите описание изображения здесь

Обновление: чтобы получить данные из вашей собственной существующей таблицы, используйте это:

 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)') FROM [MyDB].[dbo].Content CROSS APPLY CAST(content_html AS XML).nodes('/root/Physicians') AS XT(XC) CROSS APPLY XC.nodes('langAccept') AS XT2(XLang) CROSS APPLY XC.nodes('insAccept') AS XT3(XIns) 

Вы можете попробовать это. Это не точный ответ на вашу проблему, но это может помочь вам решить вашу проблему.

  DECLARE @Data XMl = '<root><Physicians><name>sajsj</name><picture/><gender/><langAccept>English</langAccept><langAccept>Spanish</langAccept> (can appear more times)<insAccept>Aetna</insAccept><insAccept>BCBS</insAccept> (can appear more times)<specialty/><specialty2/><specialty3/></Physicians></root>'; ;WITH CTE AS ( SELECT Dt.value('(name/text())[1]','VARCHAR(100)') AS Name, Dt.query('(langAccept)') AS LangAccept, Dt.query('(insAccept)') AS InsAccept FROM @Data.nodes('/root/Physicians') AS MyData(Dt) ), CteGetAllLangAccept AS ( SELECT Ct.Name, Data.Lang.value('(.)[1]', 'VARCHAR(50)') AS [LangAcceptValue], NULL AS [InsAcceptDataValue] FROM CTE Ct CROSS APPLY Ct.LangAccept.nodes('/langAccept') AS Data(Lang) ), CteGetInsAcceptData AS ( SELECT Ct.Name, NULL AS [LangAcceptValue], InsAcceptData.Ins.value('(.)[1]', 'VARCHAR(50)') AS [InsAcceptDataValue] FROM CTE Ct CROSS APPLY Ct.InsAccept.nodes('/insAccept') AS InsAcceptData(Ins) ) SELECT * FROM CteGetAllLangAccept![enter image description here][1] UNION SELECT * FROM CteGetInsAcceptData; 

Проверьте вывод здесь.0

Я думаю, если вы хотите показать его на стороне клиента, проще сделать несколько запросов, один для таблицы врачей, один для langAccept и один для insAccept :

 declare @temp table (data xml) insert into @temp (data) select '<root> <Physicians> <name>House MD</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> <Physicians> <name>Paracelsus</name> <picture></picture> <gender>Male</gender> <langAccept>German</langAccept> <langAccept>Latin</langAccept> <specialty></specialty> <specialty2></specialty2> <specialty3></specialty3> </Physicians> </root>' select tcvalue('name[1]', 'nvarchar(max)') as name, tcvalue('gender[1]', 'nvarchar(max)') as gender from @temp as a cross apply a.data.nodes('root/Physicians') as t(c) select tcvalue('name[1]', 'nvarchar(max)') as name, lcvalue('.', 'nvarchar(max)') as langAccept from @temp as a cross apply a.data.nodes('root/Physicians') as t(c) cross apply tcnodes('langAccept') as l(c) select tcvalue('name[1]', 'nvarchar(max)') as name, lcvalue('.', 'nvarchar(max)') as insAccept from @temp as a cross apply a.data.nodes('root/Physicians') as t(c) cross apply tcnodes('insAccept') as l(c) 

введите описание изображения здесь