SQL 解析 XML ROW 求助

sql mysql sqlserver xml xmlhttprequest

51422 2023-06-05 00:52:25 ‧ 778 瀏覽

分享至

請問各位前輩，DB中資料為xml，目前只會把單行的xml轉換，如何使用SQL將ROW型態的xml依欄位方式呈現?

<FormFieldValue>
    <FieldItem fieldId="Visitor" enableSearch="True" fillerName="李慧" fillerUserGuid="b276-97e731ab04a0" fillerAccount="asd123456" fillSiteId="">    
     <DataGrid>
        <Row order="0">
          <Cell fieldId="Name" fieldValue="小雯" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="Vtittle" fieldValue="經理" realValue="" customValue="" enableSearch="True" />
        </Row>
        
        <Row order="1">
          <Cell fieldId="Name" fieldValue="阿良" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="Vtittle" fieldValue="協理" realValue="" customValue="" enableSearch="True" />
        </Row>
      </DataGrid>  
    </FieldItem>
 </FormFieldValue>

sql
 Select CAST(DOC AS XML).value('(//FormFieldValue/FieldItem/@fieldValue)[1]','nvarchar(max)') as Form_Number
 
From TASK
Where VERSION_ID IN(
SELECT VERSION_ID FROM FORM_VERSION
WHERE FORM_ID='ac69-b16652e2e88a')