i have xml text, and want extract data
DECLARE @d_txt VARCHAR(max)
SET @d_txt = '
<DI_List>
<SDI>
<a>6559864</a>
<DI>
<Id>3036780478</Id>
<mic>
<date>2022-11-13</date>
<kod>774673</kod>
</mic>
<mic>
<date>2022-11-11</date>
<kod>774673</kod>
</mic>
</DI>
</SDI>
</DI_List>'
DECLARE @d_xml INT
exec sp_xml_preparedocument @d_xml output, @d_txt;
SELECT *
FROM OPENXML(@d_xml, '/DI_List/SDI', 0) WITH
(
Id VARCHAR(30) 'DI/Id',
nmic XML 'DI/mic'
)
EXEC sys.sp_xml_removedocument @d_xml
i got
Id | nmic |
---|---|
3036780478 | <mic><date>2022-11-13</date><kod>774673</kod></mic> |
but i want nmic field contains both nodes, not first only
Id | nmic |
---|---|
3036780478 | <mic><date>2022-11-13</date><kod>774673</kod></mic><mic><date>2022-11-11</date><kod>774673</kod></mic> |
can i doit with SQL Server OPENXML?
As mentioned in the comments, use XQuery instead of OPENXML
; OPENXML
is a far older and "clunky" way of querying XML. Also, your XML is in the wrong datatype, there is an xml
data type for XML.
If you switch to XQuery, and change the data type, this is much easier:
DECLARE @d_txt xml --varchar is the wrong datatype
SET @d_txt = '
<DI_List>
<SDI>
<a>6559864</a>
<DI>
<Id>3036780478</Id>
<mic>
<date>2022-11-13</date>
<kod>774673</kod>
</mic>
<mic>
<date>2022-11-11</date>
<kod>774673</kod>
</mic>
</DI>
</SDI>
</DI_List>';
SELECT SDI.DI.value('(Id/text())[1]', 'bigint') AS Id,
SDI.DI.query('mic') AS nmic
FROM @d_txt.nodes('DI_List/SDI/DI')SDI(DI);