How to ensure that SQL can read all XML tag data

Question

I have the following XML data in an 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> 

langAccept and insAccept can appear several times, and there is no way to know how many times.

I have the following SQL query that currently does not account for the langAccept and 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] 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] 

I will take this data and write to the relay on my ASP.net page, using C # as the code.

How can I change my SQL query so that it langAccept value for each langAccept and insAccept (as many times as it appears).

Answer 1

You can handle any number of nodes that could repeat, but remember that this always creates many rows for a single <Physician> entry.

Try the following:

 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) 

Using .nodes() on langAccept and insAccept inside the <Physician> node, you will get all the defined values ​​- but you will get several relational strings for one <Physican> node:

Update: to get data from your own existing table, use 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)') 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) 

Answer 2

You can try this. This is not an exact answer to your problem, but it can help you solve your problem.

  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; 

Answer 3

I think if you want to show it on the client side, it’s easier to make several queries, one for the doctors table, one for langAccept and one for 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) 

---