如何在SQL Server中读取GProposal结构的XML数据？

Got it, let's break down how to parse your XML structure in SQL Server. I'll cover two common approaches: XQuery (the modern, recommended method) and OPENXML (legacy support).

First, let's use your provided XML snippet (I've completed the truncated part for clarity):

<GProposal>
  <UnderwritingMessages>
    <anyType xsi:type="xsd:string">3425：入院补偿一次性金额超过承保限额，需进行承保查询。</anyType>
    <anyType xsi:type="xsd:string">3428：门诊补偿一次性金额超过承保限额，需进行承保查询。</anyType>
  </UnderwritingMessages>
  <Plans>
    <Plan>
      <UnderwritingMessages />
      <InsuredGroups>
        <InsuredGroup>
          <UnderwritingMessages>
            <anyType xsi:type="xsd:string">342025：入院补偿一次性金额超过承保限额，需进行承保查询。</anyType>
          </UnderwritingMessages>
        </InsuredGroup>
      </InsuredGroups>
    </Plan>
  </Plans>
</GProposal>

Approach 1: XQuery (Recommended) #

XQuery is built into SQL Server 2005+ and is more efficient, readable, and maintainable than OPENXML. Here's how to extract messages from different levels of your XML:

Step 1: Declare the XML variable (or use a table column) #

First, store your XML in a variable (or if it's in a table, skip this and use the column directly):

DECLARE @xml XML = '
<GProposal>
  <UnderwritingMessages>
    <anyType xsi:type="xsd:string">3425：入院补偿一次性金额超过承保限额，需进行承保查询。</anyType>
    <anyType xsi:type="xsd:string">3428：门诊补偿一次性金额超过承保限额，需进行承保查询。</anyType>
  </UnderwritingMessages>
  <Plans>
    <Plan>
      <UnderwritingMessages />
      <InsuredGroups>
        <InsuredGroup>
          <UnderwritingMessages>
            <anyType xsi:type="xsd:string">342025：入院补偿一次性金额超过承保限额，需进行承保查询。</anyType>
          </UnderwritingMessages>
        </InsuredGroup>
      </InsuredGroups>
    </Plan>
  </Plans>
</GProposal>';

Step 2: Extract top-level UnderwritingMessages #

Use .nodes() to shred the XML into rows, then .value() to get the text content:

SELECT
    msg.value('.', 'NVARCHAR(MAX)') AS TopLevelUnderwritingMessage
FROM @xml.nodes('/GProposal/UnderwritingMessages/anyType') AS MessageNodes(msg);

Step 3: Extract messages from InsuredGroup level #

Adjust the XPath to target the nested messages:

SELECT
    msg.value('.', 'NVARCHAR(MAX)') AS InsuredGroupUnderwritingMessage
FROM @xml.nodes('/GProposal/Plans/Plan/InsuredGroups/InsuredGroup/UnderwritingMessages/anyType') AS MessageNodes(msg);

If your XML is stored in a table #

If the XML is in a table column (e.g., YourTable.XmlData), use CROSS APPLY to join with the shredded nodes:

SELECT
    msg.value('.', 'NVARCHAR(MAX)') AS UnderwritingMessage
FROM YourTable
CROSS APPLY XmlData.nodes('/GProposal/UnderwritingMessages/anyType') AS MessageNodes(msg);

Approach 2: OPENXML (Legacy) #

OPENXML is an older method that requires preparing a document handle. It's less efficient than XQuery, but useful if you need compatibility with very old SQL Server versions:

DECLARE @xml XML = '...'; -- Same XML as before
DECLARE @docHandle INT;

-- Prepare the XML document for parsing
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml;

-- Extract top-level messages
SELECT
    Message AS TopLevelUnderwritingMessage
FROM OPENXML(@docHandle, '/GProposal/UnderwritingMessages/anyType', 2)
WITH (Message NVARCHAR(MAX) '.');

-- Extract InsuredGroup messages
SELECT
    Message AS InsuredGroupUnderwritingMessage
FROM OPENXML(@docHandle, '/GProposal/Plans/Plan/InsuredGroups/InsuredGroup/UnderwritingMessages/anyType', 2)
WITH (Message NVARCHAR(MAX) '.');

-- Clean up the document handle to free memory
EXEC sp_xml_removedocument @docHandle;

Key Tips #

• Use NVARCHAR(MAX) to handle long message texts without truncation.
• Adjust the XPath expressions to match your full XML structure (I assumed the truncated part follows the same pattern).
• Prefer XQuery over OPENXML for most cases—it's faster, requires less boilerplate, and is easier to debug.

内容的提问来源于stack exchange，提问作者Kapil