Question

在紧凑的限期下,我知道我最终能够看到这一点,但我没有多少时间独自这样做。

我有一个表格,除许多其他栏目外,还有客户栏和账户编号栏。单一客户有许多账户(拥有相同客户但不同的账户号码的经理)。

对于表一的每个客户,需要采用一种储存程序,并以以下格式将数据从表上作为xml。通知所有客户账户的xml。

<Accounts>
 <Account>
  <AccountNumber>12345</AccountNumber>
  <AccountStatus>Open</AccountStatus>
 </Account>
 <Account>
  <AccountNumber>54321</AccountNumber>
  <AccountStatus>Closed</AccountStatus>
 </Account>
</Accounts>

因此,我想我需要两点帮助。首先,如何以这种xml格式获取数据。我假定“我”使用的是“XML”的某些改动。另一件事是,我是如何由客户进行分类的,然后向每个客户提出质询?

Answer 1

这样做会奏效。这是治疗者为数不多的案例之一。

create table #a (CustomerID int, AccountNumber int, AccountStatus varchar(max))

insert into #a values (1,12345, Open )
insert into #a values (1,54321, Closed )
insert into #a values (2,77777, Open )
insert into #a values (2,88888, Closed )

DECLARE @CustomerID int
DECLARE @accounts xml

DECLARE CurInvoice CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT DISTINCT CustomerID 
    FROM #a  

OPEN CurInvoice

FETCH NEXT FROM CurInvoice INTO @CustomerID
WHILE @@FETCH_STATUS = 0
    BEGIN

    set @accounts = (
    select AccountNumber,AccountStatus
    from #a
    where CustomerID = @CustomerID
    for xml path( Account ), root( Accounts ))

    exec MyProc @XmlInput = @accounts


FETCH NEXT FROM CurInvoice INTO @CustomerID
END
CLOSE CurInvoice
DEALLOCATE CurInvoice

友情链接