我在 SQL 表列中有以下 XML 数据:

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

我将获取该数据并使用 C# 作为代码隐藏写入我的 ASP.net 页面中的转发器。

我如何修改我的 SQL 查询，以便它获取每个 langAccept 和 insAccept 标记的值(与它出现的次数一样多)。

最佳答案

您可以处理任意数量的可能重复的节点 - 但请注意，这将始终为单个条目创建大量行 <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)

关于c# - 如何确保 SQL 能够读取所有 XML 标记数据，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/26426412/