如何在sql中读取xml文件 [英] how to read xml file in sql

问题描述

您好，

如何读取存储在一个表中的xml stringin sql

Hello,
How to read this xml stringin sql which is stored in one table

<ClinicalDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:hl7-org:v3" xmlns:voc="urn:hl7-org:v3/voc" xmlns:sdtc="urn:hl7-org:sdtc">

  <realmCode code="US" />
  <typeId root="2.16.840.1.113883.1.3" extension="POCD_HD000040" />

  <templateId root="2.16.840.1.113883.10.20.22.1.1" />

  <templateId root="2.16.840.1.113883.10.20.24.1.1" />

  <templateId root="2.16.840.1.113883.10.20.24.1.2" />

  <id root="5b010313-eff2-432c-9909-6193d8416fac" />

  <code code="55182-0" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="Quality Measure Report" />
  <title>QRDA Incidence Report</title>
  </ClinicalDocument>

我试过这段代码，但它显示空值

I tried this code but it show null value

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM HPN_CQM_XMLData where Id=3

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, '/ROOT/ClinicalDocument')
WITH
(
Title varchar(50) 'title'
)

EXEC sp_xml_removedocument @hDoc
GO

推荐答案

检查这个问题，同样的apporach也可以用于SQL 2008.



如果xml在SQL Server 2012中有冒号，如何解析XML [ ^ ]



编辑：



像这样更改你的XML

Check this question, Same apporach can be used for SQL 2008 as well.

How to parse XML when xml has colon in SQL server 2012[^]

Edited:

Change your XML like this

<ClinicalDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:v3="urn:hl7-org:v3" xmlns:voc="urn:hl7-org:v3/voc" xmlns:sdtc="urn:hl7-org:sdtc">
 
  <realmCode code="US" />
  <typeId root="2.16.840.1.113883.1.3" extension="POCD_HD000040" />
 
  <templateId root="2.16.840.1.113883.10.20.22.1.1" />
 
  <templateId root="2.16.840.1.113883.10.20.24.1.1" />
 
  <templateId root="2.16.840.1.113883.10.20.24.1.2" />
 
  <id root="5b010313-eff2-432c-9909-6193d8416fac" />
 
  <code code="55182-0" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="Quality Measure Report" />
  <title>QRDA Incidence Report</title>
  </ClinicalDocument>

并尝试下面的脚本

and try below script

DECLARE @XMLSTRING XML = 
  '<ClinicalDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:v3="urn:hl7-org:v3" xmlns:voc="urn:hl7-org:v3/voc" xmlns:sdtc="urn:hl7-org:sdtc">
  <realmCode code="US" />
  <typeId root="2.16.840.1.113883.1.3" extension="POCD_HD000040" />
  <templateId root="2.16.840.1.113883.10.20.22.1.1" />
  <templateId root="2.16.840.1.113883.10.20.24.1.1" />
  <templateId root="2.16.840.1.113883.10.20.24.1.2" />
  <id root="5b010313-eff2-432c-9909-6193d8416fac" />
  <code code="55182-0" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="Quality Measure Report" />
   <title>QRDA Incidence Report</title>
   </ClinicalDocument>'
 
SELECT  T.C.query('node()')
from	@XMLSTRING.nodes('/ClinicalDocument') as T(C)

这篇关于如何在sql中读取xml文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！