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

Question

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

Hard

This should duplicate rows or put into one row (like English, Spanish) ? – Choi

If I can even take them put it in a column by CSV, I can manipulate them in the code-behind. I think adding a column and inserting each occurance by CSV is the better route for my purpose. – Hard

Column values that uses comma separators are not a good idea. That's an example of a poor database design. Instead, you should seek to have a properly normalized database, it will be much easier to obtain data from. – Sortilege

Because it is the first time I am using it, I an open for suggestion and helped :). Pretty much this query will return the result which I will be displaying it in the front end as a result. Sometime those tags will appear 10+ times, so does it make sense to create 10+ rows in a table? – Hard

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

Sausa

Unfortunately the XML is being read from a SQL table and I am not sure how many times the TAG will appear. I updated my question, the XML is already inside a SQL table column and not in a file, my apology. – Hard

@SearchForKnowledge: my post works off of a SQL Server table .... just replace my @Content with [MYDB].[dbo].[content] and off you go – Sausa

DECLARE @Content [dbo].[content]? – Hard

@SearchForKnowledge: no - replace my @Content in the SELECT statement with your own database name .... updated my response – Sausa

So I can get all the data in that case. So I don't have to use .query() anymore and don't need this anymore: /root/Physicians/OfficeLocations/office1/a? – Hard

I get the following error: The XMLDT method 'nodes' can only be invoked on columns of type xml. and for the Physician Name I am getting this error: Invalid column name 'XmlData'. – Hard

How do I cross apply other columns as well? – Hard

Well first of all: you need to use your column name (instead of XmlData). And secondly: this column has to be of type XML for this to work! – Sausa

Can I cast it as XML inside the query? changing to content_html as the column name removed the first error. – Hard

Yes - if you must - but really: if the data looks like XML, quacks like XML, smells like XML - it's XML and should be stored as XML !! – Sausa

It is from a CMS so unfortunately I do not have any control. How would I convert it to XML in this case? – Hard

I am getting an error: Incorrect Syntax near the keyword 'AS' (Thank you for the edit BTW) – Hard

@Sausa Even I am getting the same error Incorrect Syntax near the keyword 'AS' Can you help out please ? – Exhilaration

@Sausa one of the best answers (have seen like 50 of them), very practical, good balance of simplicity and advanced techniques – Tyranny

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

Discard

Thank you for the response. The XML data is already inside a SQL table column and I am just reading from the column and stripping out the data. I have no doubt it works but mine isn't a file but a table column. How can I change the @Data to point to the table column rather than hard coded string? Should I get the value from the column and store it in a string and use that string for the @Data instead and use the above query? – Hard

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

---

Inorganic

Thank you. The XML data is a column in a table so I would have to pull the column from the table and then extract – Hard