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

asp.net, c#, sql-server, xml

Solution

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)

Problem

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 '%'+@strSpecialty+'%') AND (content_html LIKE '%'+@strLocation+'%') 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).

Original source