So i have an XML file which looks like below

declare @xml
xml= '<ENVELOPE>
    <BILLFIXED>
        <BILLDATE>29-Jun-2019</BILLDATE>
        <BILLREF>123</BILLREF>
        <BILLPARTY>ABC</BILLPARTY>
    </BILLFIXED>
    <BILLOP>200</BILLOP>
    <BILLCL>200</BILLCL>
    <BILLDUE>29-Jun-2019</BILLDUE>
    <BILLOVERDUE>1116</BILLOVERDUE>
    <BILLFIXED>
        <BILLDATE>30-Jun-2019</BILLDATE>
        <BILLREF>April To June -19</BILLREF>
        <BILLPARTY>efg</BILLPARTY>
    </BILLFIXED>
    <BILLOP>100</BILLOP>
    <BILLCL>100</BILLCL>
    <BILLDUE>30-Jun-2019</BILLDUE>
    <BILLOVERDUE>1115</BILLOVERDUE>
</ENVELOPE>

Im trying to read this using openxml

DECLARE  @hDoc AS INT, @SQL NVARCHAR (MAX) 

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML 

select   BILLDATE, BILLREF, BILLPARTY, BILLOP,BILLCL,BILLDUE, BILLOVERDUE
from OPENXML(@hDoc, '//BILLFIXED') 
WITH  
( 
BillDate [varchar](50) 'BILLDATE', 
BIllREF [varchar](50) 'BILLREF', 
BILLPARTY [varchar](100) 'BILLPARTY' 
,BILLOP [varchar](100) 'BILLOP' 
 BILLCL[varchar](100) 'REFERENCE', 
 BILLDUE [varchar](100) 'BILLDUE', 
 BILLOVERDUE [varchar](100) 'BILLOVERDUE'
) 

It was easy to extract <BILLFIXED> tag but not able to access the siblings tags

<BILLCL>
<BILLDUE>
<BILLOVERDUE>

any help in accessing these tags Thanks

CodePudding user response：

Microsoft proprietary OPENXML() and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases. Starting from SQL Server 2005 onwards, it is strongly recommended to re-write your SQL and switch it to XQuery. Also, OPENXML() cannot take advantage of XML indexes while XQuery methods can.

Please try the following solution.

The XML sample is flattened.

So, we are using full power of XQuery.

SQL

DECLARE @xml XML = 
N'<ENVELOPE>
    <BILLFIXED>
        <BILLDATE>29-Jun-2019</BILLDATE>
        <BILLREF>123</BILLREF>
        <BILLPARTY>ABC</BILLPARTY>
    </BILLFIXED>
    <BILLOP>200</BILLOP>
    <BILLCL>200</BILLCL>
    <BILLDUE>29-Jun-2019</BILLDUE>
    <BILLOVERDUE>1116</BILLOVERDUE>
    <BILLFIXED>
        <BILLDATE>30-Jun-2019</BILLDATE>
        <BILLREF>April To June -19</BILLREF>
        <BILLPARTY>efg</BILLPARTY>
    </BILLFIXED>
    <BILLOP>100</BILLOP>
    <BILLCL>100</BILLCL>
    <BILLDUE>30-Jun-2019</BILLDUE>
    <BILLOVERDUE>1115</BILLOVERDUE>
</ENVELOPE>';

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Nmbr 
    , c.value('(./BILLDATE/text())[1]', 'VARCHAR(20)') AS BILLDATE
    , c.value('(./BILLREF/text())[1]', 'VARCHAR(20)') AS BILLREF
    , c.value('(./BILLPARTY/text())[1]', 'VARCHAR(20)') AS BILLPARTY
    , c.value('(/ENVELOPE/*[sql:column("seq.pos")]/text())[1]', 'INT') AS BILLOP
    , c.value('(/ENVELOPE/*[sql:column("seq.pos")   1]/text())[1]', 'INT') AS BILLCL
    , c.value('(/ENVELOPE/*[sql:column("seq.pos")   2]/text())[1]', 'VARCHAR(20)') AS BILLDUE
    , c.value('(/ENVELOPE/*[sql:column("seq.pos")   3]/text())[1]', 'INT') AS BILLOVERDUE
    , seq.pos   -- just to see
FROM @xml.nodes('/ENVELOPE/BILLFIXED') AS t(c)
   CROSS APPLY (SELECT t.c.value('let $n := . return count(/ENVELOPE/*[. << $n[1]])   2','INT') AS pos
         ) AS seq;

Output

CodePudding user response：

I agree you should not use OPENXML, and instead use .nodes and .value.

Unfortunately, SQL Server does not allow the sibling:: axis in XQuery, which would have made this much easier.

You can do this purely using XQuery, by using the >> positional predicate.

SELECT
      c.value('(BILLDATE /text())[1]', 'VARCHAR(20)') AS BILLDATE
    , c.value('(BILLREF  /text())[1]', 'VARCHAR(20)') AS BILLREF
    , c.value('(BILLPARTY/text())[1]', 'VARCHAR(20)') AS BILLPARTY
    , c.value('let $i := . return (/ENVELOPE/BILLOP     [. >> $i]/text())[1]', 'INT') AS BILLOP
    , c.value('let $i := . return (/ENVELOPE/BILLCL     [. >> $i]/text())[1]', 'INT') AS BILLCL
    , c.value('let $i := . return (/ENVELOPE/BILLDUE    [. >> $i]/text())[1]', 'VARCHAR(20)') AS BILLDUE
    , c.value('let $i := . return (/ENVELOPE/BILLOVERDUE[. >> $i]/text())[1]', 'INT') AS BILLOVERDUE
FROM @xml.nodes('/ENVELOPE/BILLFIXED') AS t(c);

What this does is as follows:

• Shred just the /ENVELOPE/BILLFIXED nodes.
• For each of those, return the children as normal.
• For each sibling, do the following steps:
  • Store the current node in $i.
  • Take the first child node matching the correct name, of the parent,...
  • ...where that node is positioned after $i, so [. >> $i]
  • Take the first child node's text [1]

db<>fiddle