More Help needed in XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

More Help needed in XML

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-06-26 : 15:33:00

<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
<Data>
<Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" >
<NewAdvices>
<DDICAdvice>
<SeqNo>123456</SeqNo>
<SUReference>000001</SUReference>
<ReasonCode>2</ReasonCode>
<NoOfAdvForClaim>1</NoOfAdvForClaim>
<TotalAmount>40.00</TotalAmount>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-04-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<DDICAdvice>
<SeqNo>75758</SeqNo>
<SUReference>32544</SUReference>
<ReasonCode>2</ReasonCode>
<NoOfAdvForClaim>1</NoOfAdvForClaim>
<TotalAmount>20.00</TotalAmount>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices>
<TotalValueOfDebits>60.00</TotalValueOfDebits>
<DateOfDebit>2014-05-30</DateOfDebit>
</NewAdvices>
</Document>
</Data>
</VocaDocument>

I need to get the DateOfDirectDebit and Amount for each SeqNo to load into a temp table. This is section of a larger report and Im trying to break it up to send out reports to different customers

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-26 : 16:29:56

Here is an example that works. I would recommend replacing the '//' that I have in the FROM clause with the exact path.

DECLARE @x XML = 
'<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
  <Data>
    <Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT">
      <NewAdvices>
        <DDICAdvice>
          <SeqNo>123456</SeqNo>
          <SUReference>000001</SUReference>
          <ReasonCode>2</ReasonCode>
          <NoOfAdvForClaim>1</NoOfAdvForClaim>
          <TotalAmount>40.00</TotalAmount>
          <DDCollections>
            <DDCollection>
              <DateOfDirectDebit>2014-04-01</DateOfDirectDebit>
              <Amount>20.00</Amount>
            </DDCollection>
            <DDCollection>
              <DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
              <Amount>20.00</Amount>
            </DDCollection>
          </DDCollections>
        </DDICAdvice>
        <DDICAdvice>
          <SeqNo>75758</SeqNo>
          <SUReference>32544</SUReference>
          <ReasonCode>2</ReasonCode>
          <NoOfAdvForClaim>1</NoOfAdvForClaim>
          <TotalAmount>20.00</TotalAmount>
          <DDCollections>
            <DDCollection>
              <DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
              <Amount>20.00</Amount>
            </DDCollection>
          </DDCollections>
        </DDICAdvice>
        <TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices>
        <TotalValueOfDebits>60.00</TotalValueOfDebits>
        <DateOfDebit>2014-05-30</DateOfDebit>
      </NewAdvices>
    </Document>
  </Data>
</VocaDocument>';

SELECT
	c1.value('SeqNo[1]','varchar(32)') AS SeqNo,
	c2.value('DateOfDirectDebit[1]','varchar(32)') AS DateOfDirectDebit,
	c2.value('Amount[1]','varchar(32)') AS Amount
INTO #YourTempTable
FROM
	@x.nodes('//DDICAdvice') T1(c1)
	CROSS APPLY c1.nodes('//DDCollection') T2(c2);

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-06-26 : 18:55:48

HI,

I have tried to incorporate that in my code but the results are incorrect. I have imported the xml into a temptable as I amend some details, can you help

CREATE TABLE #WORKINGXML
(
XMLTEXT XML
)

SELECT
a.c.value('SeqNo[1]','varchar(32)') AS SeqNo,
t2.c2.value('DateOfDirectDebit[1]','varchar(32)') AS DateOfDirectDebit,
t2.c2.value('Amount[1]','varchar(32)') AS Amount
FROM ( SELECT XMLTEXT AS Xmlreport FROM #WORKINGXML) d
CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice') AS a(c)
CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice/DDCollections/DDCollection') T2(c2);

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-06-27 : 07:46:21

I have tried the following

<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
<Data>
<Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" created="2014-05-10T00:24:40" schemaVersion="1.0">
<CompanyName>Bacs Payment Schemes Limited</CompanyName>
<NewAdvices>
<DDICAdvice>
<SeqNo>2014050903A889958164</SeqNo>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-04-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<DDICAdvice>
<SeqNo>2014050903A889958170</SeqNo>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices>
<TotalValueOfDebits>60.00</TotalValueOfDebits>
<DateOfDebit>2014-05-30</DateOfDebit>
</NewAdvices>
</Document>
</Data>
</VocaDocument>

CREATE TABLE #WORKINGXML
(
XMLTEXT XML
)

/* Load XML into temporary table */
INSERT INTO #WORKINGXML
SELECT XMLTEXT FROM @NEWDDICtable
/* REMOVE SIGNATURE NODE */
Set @SQL = 'UPDATE #WORKINGXML SET XMLText.modify(''delete /VocaDocument/Signature'')'
exec sp_executesql @SQL
Set @SQL = 'UPDATE #WORKINGXML SET XMLText.modify(''delete /VocaDocument/SignatureMethod'')'
exec sp_executesql @SQL
IF @@ERROR <> 0

SELECT
a.c.value('SeqNo[1]','varchar(32)') AS SeqNo,
t2.c2.value('DateOfDirectDebit[1]','varchar(32)') AS DateOfDirectDebit,
t2.c2.value('Amount[1]','varchar(32)') AS Amount
FROM ( SELECT XMLTEXT AS Xmlreport FROM #WORKINGXML) d
CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice') AS a(c)
CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document/NewAdvices/DDICAdvice/DDCollections/DDCollection') T2(c2)
WHERE a.c.value('SeqNo[1]', 'varchar(32)') = '2014050903A889958164'

But I get the following results

SeqNo DateOfDirectDebit Amount
2014050903A889958164 2014-04-01 20.00
2014050903A889958164 2014-05-01 20.00
2014050903A889958164 2014-05-01 20.00

I should only get two records returned

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-27 : 08:49:41

In the second cross apply don't go back to the root node, change it to

...
CROSS APPLY c.nodes('DDCollections/DDCollection') T2 ( c2 )
...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-27 : 09:10:13

[code]DECLARE @Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Data XML NOT NULL
);

INSERT @Sample
(
Data
)
VALUES (' <VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd">
<Data>
<Document type="DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT" created="2014-05-10T00:24:40" schemaVersion="1.0">
<CompanyName>Bacs Payment Schemes Limited</CompanyName>
<NewAdvices>
<DDICAdvice>
<SeqNo>2014050903A889958164</SeqNo>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-04-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<DDICAdvice>
<SeqNo>2014050903A889958170</SeqNo>
<DDCollections>
<DDCollection>
<DateOfDirectDebit>2014-05-01</DateOfDirectDebit>
<Amount>20.00</Amount>
</DDCollection>
</DDCollections>
</DDICAdvice>
<TotalNumberOfNewAdvices>2</TotalNumberOfNewAdvices>
<TotalValueOfDebits>60.00</TotalValueOfDebits>
<DateOfDebit>2014-05-30</DateOfDebit>
</NewAdvices>
</Document>
</Data>
</VocaDocument>');

-- SwePeso
DECLARE @SeqNo VARCHAR(32) = '2014050903A889958164';

SELECT a.n.value('SeqNo[1]', 'VARCHAR(32)') AS SeqNo,
b.n.value('DateOfDirectDebit[1]', 'DATETIME') AS DateOfDirectDebit,
b.n.value('Amount[1]', 'MONEY') AS Amount
FROM @Sample AS s
CROSS APPLY Data.nodes('(VocaDocument/Data/Document/NewAdvices/DDICAdvice[SeqNo=sql:variable("@SeqNo")])') AS a(n)
CROSS APPLY a.n.nodes('(DDCollections/DDCollection)') AS b(n);

SET @SeqNo = '2014050903A889958170';

SELECT a.n.value('SeqNo[1]', 'VARCHAR(32)') AS SeqNo,
b.n.value('DateOfDirectDebit[1]', 'DATETIME') AS DateOfDirectDebit,
b.n.value('Amount[1]', 'MONEY') AS Amount
FROM @Sample AS s
CROSS APPLY Data.nodes('(VocaDocument/Data/Document/NewAdvices/DDICAdvice[SeqNo=sql:variable("@SeqNo")])') AS a(n)
CROSS APPLY a.n.nodes('(DDCollections/DDCollection)') AS b(n);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-27 : 10:43:14

quote:
SELECT a.n.value('SeqNo[1]', 'VARCHAR(32)') AS SeqNo,
b.n.value('DateOfDirectDebit[1]', 'DATETIME') AS DateOfDirectDebit,
b.n.value('Amount[1]', 'MONEY') AS Amount
FROM @Sample AS s
CROSS APPLY Data.nodes('(VocaDocument/Data/Document/NewAdvices/DDICAdvice[SeqNo=sql:variable("@SeqNo")])') AS a(n)
CROSS APPLY a.n.nodes('(DDCollections/DDCollection)') AS b(n);

https://www.youtube.com/watch?v=218iXiKhKlg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources