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

I have the following XML data in a 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 and insAccept can appear multiple times, and there is no way to know how many times.

I have the following SQL query which currently is not taking into account ‘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 '%'<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f9d2b98a8d8baa899c9a9098958d80">[email protected]</a>+'%')
    AND
    (content_html LIKE '%'<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="446f04373036082b2725302d2b2a">[email protected]</a>+'%')
    AND
    (content_status = 'A')
ORDER BY
    [content_title]

I will be taking that data and writing to a repeater in my ASP.net page using C# as code-behind.

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can handle any number of nodes that might repeat – but mind you, this will always create numerous rows for a single entry <Physician>.

Try this:

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)

By using the .nodes() on both the langAccept and insAccept inside the <Physician> node, you get all the defined values – but you end up with several relational rows for a single <Physican> node:

Update: to get the 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)

Method 2

You can try like this.
This is not the exact answer to your problem, but this might 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;

Method 3

I think if you want to show it on the client side, it’s easier to do several queries, one for doctors table, one for langAccept and one for insAccept:

declare @temp table (data xml)

insert into @temp (data)
select '<root>
    <Physicians>
        <name>House M.D.</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
    t.c.value('name[1]', 'nvarchar(max)') as name,
    t.c.value('gender[1]', 'nvarchar(max)') as gender
from @temp as a
    cross apply a.data.nodes('root/Physicians') as t(c)
    
select
    t.c.value('name[1]', 'nvarchar(max)') as name,
    l.c.value('.', 'nvarchar(max)') as langAccept
from @temp as a
    cross apply a.data.nodes('root/Physicians') as t(c)
    cross apply t.c.nodes('langAccept') as l(c)
    
select
    t.c.value('name[1]', 'nvarchar(max)') as name,
    l.c.value('.', 'nvarchar(max)') as insAccept
from @temp as a
    cross apply a.data.nodes('root/Physicians') as t(c)
    cross apply t.c.nodes('insAccept') as l(c)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0